topic Re: Dynamically adding current week snapshot to an archive in App Development

Dynamically adding current week snapshot to an archive

tarteflambee — Mon, 15 Jan 2024 15:49:44 GMT

Hi everyone,

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

On 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 :

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...

Re: Dynamically adding current week snapshot to an archive

Rohan — Wed, 17 Jan 2024 09:00:48 GMT

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.

Re: Dynamically adding current week snapshot to an archive

Rohan — Wed, 17 Jan 2024 09:02:42 GMT

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

Regards,

Rohan.