Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

valpassos
New Contributor III

Dealing with a crosstable in model architecting

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


1 Solution

Accepted Solutions

Re: Dealing with a crosstable in model architecting

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

4 Replies

Re: Dealing with a crosstable in model architecting

Have you already read this?

The Crosstable Load

valpassos
New Contributor III

Re: Dealing with a crosstable in model architecting

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

Re: Dealing with a crosstable in model architecting

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
New Contributor III

Re: Dealing with a crosstable in model architecting

Thanks Sunny!

Community Browser