topic Change data relationship between 2 fields in the same table. in Connectivity & Data Prep

Change data relationship between 2 fields in the same table.

kevinwh15 — Wed, 05 Jun 2019 15:34:20 GMT

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.

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

dplr-rn — Wed, 05 Jun 2019 17:25:07 GMT

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

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

kevinwh15 — Wed, 05 Jun 2019 17:36:55 GMT

Thanks Dilipranijith,

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

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

NZFei — Thu, 06 Jun 2019 03:47:46 GMT

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;

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

Custard89 — Thu, 06 Jun 2019 04:59:07 GMT

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