Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Somewhat new to Qlik and I am not sure how to ask the question to the problem i need solved but i will do my best to explain what i am trying to do.
I have three Field that i load in on the script editor from an SQL severe as followed:
Load
"F4 DATE",
Shift,
"Projected Sales";
In a table the data is expressed as followed:
F4 DATE | Shift | Projected Sales |
5/3/2019 | 1 | $1,000 |
5/3/2019 | 2 | $200 |
5/3/2019 | 3 | $500 |
5/4/2019 | 1 | $900 |
5/4/2019 | 2 | $300 |
5/4/2019 | 3 | $600 |
5/5/2019 | 1 | $1,100 |
5/5/2019 | 2 | $500 |
5/5/2019 | 3 | $900 |
I would like to be able to represent the data like this
F4 Date | Shift 1 | Shift 2 | Shift 3 |
5/3/2019 | $1,000 | $200 | $500 |
5/4/2019 | $900 | $300 | $600 |
5/5/2019 | $1,100 | $500 | $900 |
I know there is a way using pivot tables the problem is that I am also bring in actual sale from another table that already has the data separated by Shifts so I am hoping there is a fix that i can do in the Load editor.
Any help on this would be fantastic and let me know if you need me to clarify anything.
Thanks in advance,
Kevin S.
You only have three shifts a day, right? If so, the following script should work for you:
Temp:
LOAD
[F4 DATE],
Shift,
[Projected Sales]
FROM
Your data source;
Table:
load Distinct
[F4 DATE]
Resident Temp;
left join
load
[F4 DATE],
[Projected Sales] as [Shift 1]
Resident Temp
where Shift=1;
left join
load
[F4 DATE],
[Projected Sales] as [Shift 2]
Resident Temp
where Shift=2;
left join
load
[F4 DATE],
[Projected Sales] as [Shift 3]
Resident Temp
where Shift=3;
drop table Temp;
Or if you want to generic load, you can use this scipt:
Generic LOAD
[F4 DATE],
'Shift ' & Shift as WhateverFieldName,
[Projected Sales]
FROM
Your data source;