Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fordy39
New Contributor

Data Structure on import

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.

NameAgeGroupStart DateMid Term DateEnd Date
Chris12A10/11/201910/12/201910/01/2012

 

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

NameAgeGroupDate TypeDate
Chris12AStart Date10/11/2019
Chris12AMid Term Date10/12/2019
Chris12AEnd Date10/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

 

 

Labels (1)
6 Replies
Partner
Partner

Re: Data Structure on import

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
];

Partner
Partner

Re: Data Structure on import

CrossTable will be your friend in this situation:

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

 

Blog: WhereClause   Twitter: @treysmithdev
Partner
Partner

Re: Data Structure on import

You can do this with crosstable.

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

Load *

From ...

 

Partner
Partner

Re: Data Structure on import

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;

Partner
Partner

Re: Data Structure on import

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
New Contributor

Re: Data Structure on import

Thanks all for the response!

I went for the unpivot option but all appreciated

Chris