Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
gilldilpreet
Contributor III
Contributor III

Transposing data in Qlikview

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:

NameGo KartingDate CompletedScuba DiveDate CompletedZip lineDate CompletedSkydiveDate Completed
JohnYes18/05/2021No Yes23/10/2020No 
LucyYes09/02/2019Yes23/09/2020No Yes23/04/2021
DanielNo No No Yes15/09/2019
SarahNo No No No 
SamanthaNo Yes13/08/2019No Yes04/07/2021
JamesNo Yes20/04/2020Yes13/12/2020Yes06/03/2019

 

After:

NameActivityDate completed
JohnYes18/05/2021
LucyYes09/02/2019
DanielNo 
SarahNo 
SamanthaNo 
JamesNo 
JohnNo 
LucyYes23/09/2020
DanielNo 
SarahNo 
SamanthaYes13/08/2019
JamesYes20/04/2020
JohnYes23/10/2020
LucyNo 
DanielNo 
SarahNo 
SamanthaNo 
JamesYes13/12/2020
JohnNo 
LucyYes23/04/2021
DanielYes15/09/2019
SarahNo 
SamanthaYes04/07/2021
JamesYes06/03/2019

 

Many thanks

Dilpreet 

Labels (1)
3 Replies
nsm1234567
Creator II
Creator II

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;

saran7de
Master
Master

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;
saran7de
Master
Master

Output:

commQV71.PNG