Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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
Highlighted

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

View solution in original post

4 Replies
Highlighted

Re: Dealing with a crosstable in model architecting

Have you already read this?

The Crosstable Load

Highlighted
Creator II
Creator II

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

Highlighted

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

View solution in original post

Highlighted
Creator II
Creator II

Re: Dealing with a crosstable in model architecting

Thanks Sunny!