Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Highlighted
kevinwh15
New Contributor

Change data relationship between 2 fields in the same table.

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 DATEShiftProjected Sales
5/3/20191$1,000
5/3/20192$200
5/3/20193$500
5/4/20191$900
5/4/20192$300
5/4/20193$600
5/5/20191$1,100
5/5/20192$500
5/5/20193$900

 

I would like to be able to represent the data like this

F4 DateShift 1Shift 2Shift 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.

4 Replies
Partner
Partner

Re: Change data relationship between 2 fields in the same table.

What you are attempting is the opposite of cross table load. you need generic load
check below blog for theory and method
https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470
kevinwh15
New Contributor

Re: Change data relationship between 2 fields in the same table.

Thanks Dilipranijith,

This looks like what I am trying to accomplish. I am going to give it a try.
Partner
Partner

Re: Change data relationship between 2 fields in the same table.

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;

Custard89
New Contributor

Re: Change data relationship between 2 fields in the same table.

Yes me too will be trying..This is the solution i was searching since along time...Will be trying soon mykfcexperience