topic Re: Transposing data in Qlikview in QlikView

Transposing data in Qlikview

gilldilpreet — Tue, 18 May 2021 09:49:51 GMT

Hi,

I currently have an excel dataset and am trying to transpose the data as shown below but in Qlikview. Any ideas on how this can be achieved would be greatly appreciated!

Before:

Name	Go Karting	Date Completed	Scuba Dive	Date Completed	Zip line	Date Completed	Skydive	Date Completed
John	Yes	18/05/2021	No		Yes	23/10/2020	No
Lucy	Yes	09/02/2019	Yes	23/09/2020	No		Yes	23/04/2021
Daniel	No		No		No		Yes	15/09/2019
Sarah	No		No		No		No
Samantha	No		Yes	13/08/2019	No		Yes	04/07/2021
James	No		Yes	20/04/2020	Yes	13/12/2020	Yes	06/03/2019

After:

Name	Activity	Date completed
John	Yes	18/05/2021
Lucy	Yes	09/02/2019
Daniel	No
Sarah	No
Samantha	No
James	No
John	No
Lucy	Yes	23/09/2020
Daniel	No
Sarah	No
Samantha	Yes	13/08/2019
James	Yes	20/04/2020
John	Yes	23/10/2020
Lucy	No
Daniel	No
Sarah	No
Samantha	No
James	Yes	13/12/2020
John	No
Lucy	Yes	23/04/2021
Daniel	Yes	15/09/2019
Sarah	No
Samantha	Yes	04/07/2021
James	Yes	06/03/2019

Many thanks

Dilpreet

Re: Transposing data in Qlikview

nsm1234567 — Tue, 18 May 2021 19:49:49 GMT

Hey there,

I don't think there are a ton of clean ways to do this as it's not a simple crosstable. I'd probably end up doing something like the attached which I've pasted the code for below. If it's not a huge data source this should be fine.

SET vActivityList = 'Go Karting','Scuba Dive','Zip line','Skydive';
SET vDateColumnList = 'Date Completed','Date Completed1','Date Completed2','Date Completed3';

for each vActivity in $(vActivityList)

let vDateColumn = Pick(match('$(vActivity)',$(vActivityList)),$(vDateColumnList));

LOAD Name,
'$(vActivity)' as [Activity Type],
[$(vActivity)] as Activity,
[$(vDateColumn)] as [Date Completed]
FROM
[C:\Users\sm0460\Desktop\excel_source.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where len([$(vActivity)]) > 0;

next vActivity;

Re: Transposing data in Qlikview

Saravanan_Desingh — Wed, 19 May 2021 02:35:06 GMT

Try this,

SET DateFormat='DD/MM/YYYY'; tab1: LOAD * INLINE [ Name, Go Karting, Date Completed1, Scuba Dive, Date Completed2, Zip line, Date Completed3, Skydive, Date Completed4 John, Yes, 18/05/2021, No, , Yes, 23/10/2020, No, Lucy, Yes, 09/02/2019, Yes, 23/09/2020, No, , Yes, 23/04/2021 Daniel, No, , No, , No, , Yes, 15/09/2019 Sarah, No, , No, , No, , No, Samantha, No, , Yes, 13/08/2019, No, , Yes, 04/07/2021 James, No, , Yes, 20/04/2020, Yes, 13/12/2020, Yes, 06/03/2019 ]; tab2: LOAD Name, [Go Karting] As Activity, [Date Completed1] As [Date Completed] Resident tab1; LOAD Name, [Scuba Dive] As Activity, [Date Completed2] As [Date Completed] Resident tab1; LOAD Name, [Zip line] As Activity, [Date Completed3] As [Date Completed] Resident tab1; LOAD Name, [Skydive] As Activity, [Date Completed4] As [Date Completed] Resident tab1; Drop Table tab1;

Re: Transposing data in Qlikview

Saravanan_Desingh — Wed, 19 May 2021 02:35:37 GMT

Output: