Dealing with a crosstable in model architecting

[valpassos]

Hi community, this is my first post here, so hello to all!

I have an excel file with this structure:

Where the Current Year always refer to the actual year (so, 2018), so PreviousYear refers to 2017, and PreviousYear2 refers to 2016, in this case. The values mean Sales.

What are the best practices regarding maintaining this type of structure? And how should I work with it in the modeling process?

Should I receive a file named Sales_2018, from which I then retrieve the Current Year and load it in the table? Should I convert it to a straight table? And when a new year comes in, so Sales_2019, how should I shift the Current Year to the Previous Year and so on? Is it only a matter of subtracting CurrentYear-1 and CurrentYear-2?

What I’m really asking is: how does an incremental load operate with this type of structure?

I would appreciate snippets of script since I'm still gaining sensitivity regarding modelling in qlik

Thanks!

Lisa

[sunny_talwar]

Have you already read this?

The Crosstable Load

[valpassos]

Hi Sunny

Indeed I have, but I think my question differs in that I don't have explicit year or month names in my columns, only the names 'Current,' 'PreviousYear' and 'PreviousYear2'. So, how should I extract the proper years considering that the file will be updated yearly?

Thanks,

Lisa

[sunny_talwar · Accepted solution]

You can use an if statement after your crosstable load....

If(Year = 'CurrentYear', Year(Today()),

     If(Year = 'PreviousYear', Year(Today()) - 1, Year(Today())-2)) as Year

Or you can use ApplyMap...

[valpassos]

Thanks Sunny!