Data Structure on import

[fordy39]

Hi, 

Im new to Qlik Sense and want to ask a question about reformatting data on import.

I have raw data that looks like this.

Name	Age	Group	Start Date	Mid Term Date	End Date
Chris	12	A	10/11/2019	10/12/2019	10/01/2012

and I would like a tabular report to show something like the following.

Name	Age	Group	Date Type	Date
Chris	12	A	Start Date	10/11/2019
Chris	12	A	Mid Term Date	10/12/2019
Chris	12	A	End Date	10/01/2012

Ive tried to pivot the table, but cant seem to add multiple measures to the pivot table. Is there another way to do this?

Thankyou!

Chris

[StarinieriG]

Hi

you should use crosstable

CrossTable ([Date Type], Date, 3)
LOAD * Inline
[Name,Age,Group,Start Date,Mid Term Date,End Date
Chris,12,A,10/11/2019,10/12/2019,10/01/2012
];

[treysmithdev]

CrossTable will be your friend in this situation:

data:
CrossTable (DateType, Date, 3)
Load 
   Name,
   Age,
   Group,
   StartDate,
   MidTermDate,
   EndDate
From 
    [%Source];

[DavidM]

You can do this with crosstable.

Crosstable ([Date Type],[Date],3)

Load *

From ...

[diegoviana]

You can use a crosstable in the script to solve this problem:

it will be like this:

raw_data:
Load * inline

[
Name,Age,Group,Start Date,Mid Term Date,End Date
Chris,12,A,10/11/2019,10/12/2019,10/01/2012
];

Crosstable (Data_Type,Date,3) //3 = name+age+group 
LOAD
Name,
Age,
Group,
[Start Date],
[Mid Term Date],
[End Date]
Resident raw_data;

drop table raw_data;

[lironbaram]

hi 

you can even do it with out writing in the script 

load the table to data manger 

than click on unpivot - select the 3 fields you want to transform , you'll see in the bottom of the screen the result 

after applying the transformation you'll be able to rename the fields 

[fordy39]

Thanks all for the response!

I went for the unpivot option but all appreciated

Chris