Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have some data in a source where every month is a seperate column instead of a field with the date.
THis makes it very difficult to make a selection for some months (and use a master calender).
In excell everything explained (tab 1 and 2).
Current situation:
Product_Type | Description | Sales Januari | Sales Februari | Sales March | Sales April | Sales May |
A | Product A | 6 | 4 | 0 | 6 | 9 |
A2 | Product A2 | 5 | 5 | 5 | 8 | 3 |
B | Product B | 0 | 5 | 3 | 7 | 5 |
B2 | Product B2 | 3 | 3 | 4 | 4 | 7 |
C | Product C | 2 | 4 | 2 | 3 | 3 |
C2 | Product C2 | 5 | 3 | 6 | 0 | 2 |
Now it is impossible/ very difficult to make a selection for a specific range of months. That is why i prefer :
Desired situation:
Product_Type | Description | Sales | Month |
A | Product A | 6 | Januari |
A2 | Product A2 | 5 | Januari |
B | Product B | 0 | Januari |
B2 | Product B2 | 3 | Januari |
C | Product C | 2 | Januari |
C2 | Product C2 | 5 | Januari |
A | Product A | 4 | Februari |
A2 | Product A2 | 5 | Februari |
B | Product B | 5 | Februari |
B2 | Product B2 | 3 | Februari |
C | Product C | 4 | Februari |
C2 | Product C2 | 3 | Februari |
A | Product A | 0 | March |
A2 | Product A2 | 5 | March |
B | Product B | 3 | March |
B2 | Product B2 | 4 | March |
C | Product C | 2 | March |
C2 | Product C2 | 6 | March |
A | Product A | 6 | April |
A2 | Product A2 | 8 | April |
B | Product B | 7 | April |
B2 | Product B2 | 4 | April |
C | Product C | 3 | April |
C2 | Product C2 | 0 | April |
A | Product A | 9 | May |
A2 | Product A2 | 3 | May |
B | Product B | 5 | May |
B2 | Product B2 | 7 | May |
C | Product C | 3 | May |
C2 | Product C2 | 2 | May |
How can i solve this in some scripting / temp table?
Thanks in advance!
Which fields do you want to combine?
Unfortunately, you can't do a preceeding load on a crosstable, so you'll probably be looking for something like this. This will give you the output you want.
NewTable_temp:
CrossTable(Month, Sales, 2)
LOAD Product_Type,
Description,
[Sales Januari],
[Sales Februari],
[Sales March],
[Sales April],
[Sales May]
FROM [Sample_qlikview_forum_based_excel_V1_Selection_period.xlsx]
(ooxml, embedded labels, table is Source);
NewTable:
NoConcatenate
LOAD
subfield(Month, 'Sales ') as Month,
Sales,
Product_Type,
Description
RESIDENT NewTable_temp;
drop table NewTable_temp
Which fields do you want to combine?
Unfortunately, you can't do a preceeding load on a crosstable, so you'll probably be looking for something like this. This will give you the output you want.
NewTable_temp:
CrossTable(Month, Sales, 2)
LOAD Product_Type,
Description,
[Sales Januari],
[Sales Februari],
[Sales March],
[Sales April],
[Sales May]
FROM [Sample_qlikview_forum_based_excel_V1_Selection_period.xlsx]
(ooxml, embedded labels, table is Source);
NewTable:
NoConcatenate
LOAD
subfield(Month, 'Sales ') as Month,
Sales,
Product_Type,
Description
RESIDENT NewTable_temp;
drop table NewTable_temp
Thanks Kenny,
Thanks for the solution!
I did't try it yet, but understand what you have done and that is absoluty the solution
Now you introduced the "crosstable" as solution,, i also found a tutorial: https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable
Thanks for your time and i will implement it tomorrow