Dynamically adding current week snapshot to an archive

[tarteflambee]

Hi everyone,

Struggling to achieve my goal, may need your insights! Here's the case : 

On Qlik Sense Business in the data load editor (script) I have an inventory table, which always reflect today's picture (currently we are on week 3

I want to create an archive of the inventory table with 1 column = 1 week, so each new week = +1 column

So besides the key (which is my products) there are only columns that are Week numbers.

Maybe that's more easy to explain with this example : 

The reason I want all the week of the year as column (even if only null values because it's the future) is that I'm displaying the full table on the user interface with the option to hide all the null() columns. So whenever a new week is filled on the archive, it's displayed on user interface.

What I've tried : 

Concatenate -> Got duplicates

More details if you're curious/need more information :

Spoiler

My application goal is to display the rolling 5 past week of inventory. To compare the versus. [W2 vs W1], [W3 vs W2], [W4 vs W3] etc... But I'll be creating another sheet displaying archive data. I'm stuck on that blocking point.

My criteria's for the archive are : 

- If there's a new key (product) -> new line

- If for any reason there's more than 1 week missing to the archive, fill all the missing columns on the archive.

- Should be dynamic, I'll be running this everyday

Thanks a lot if you help me to crack this up...

TF

[Rohan]

Hi @tarteflambee ,

have you tried to Outerjoin the Weekly increment into your Acrhieve qvd ? Something like :

Table :

Load 

Key,

Week1,

Week2,

.. WeekN

From [Archieve.qvd];

Outer Join(Table)

Load

Key,

WeekN+1

From[New Week.qvd];

store Table into [Archieve.qvd](qvd);

drop table Table;

I believe this will resolve the blocker that you are facing. You will have to streamline this idea into your use case with a little tinkering, I believe. Do let me know if it worked out or not.

Regards,

Rohan.

[Rohan]

I also have an alternative using with Applymap() in my mind, but explaining it over text will be too lengthy.

Regards,

Rohan.