Pivot Columns to Rows Excel File

[Ily]

Hello Guys Again,

I have a new issue, i have an Excel File that i would like to transpose from Columns To Rows , I have " qualified columns inter,client contrat and diffrent mesures as below :

inter	client	contrat	prime_incapacite_2017	prime_invalidite_2017	prime_deces_2017	prime_incapacite_2018	prime_invalidite_2018	prime_deces_2018	sinistre_2017	sinistre_2018
9962	11	1	15	20	30	40	50	60	70	80
9963	112	2	116	21	31	41	51	63	72	82

To this model :

inter	client	contrat	annee	garantie	prime	sinistre
9962	11	1	2017	incapacite	15	70
9962	11	1	2017	invalidite	20	70
9962	11	1	2017	deces	30	70
9962	11	1	2018	incapacite	40	80
9962	11	1	2018	invalidite	50	80
9962	11	1	2018	deces	60	80
9963	112	2	2017	incapacite	116	72
9963	112	2	2017	invalidite	21	72
9963	112	2	2017	deces	31	72
9963	112	2	2018	incapacite	41	82
9963	112	2	2018	invalidite	51	82
9963	112	2	2018	deces	63	82

Thank you Guys in Advance 

[vchuprina · Accepted solution]

Hi,

Try this:

DATA_TMP:    
CrossTable(Data, Value, 3)
LOAD * Inline [
inter,    client,   contrat,  prime_incapacite_2017,     prime_invalidite_2017,    prime_deces_2017,    prime_incapacite_2018,     prime_invalidite_2018,    prime_deces_2018,    sinistre_2017,     sinistre_2018
9962, 11,  1,   15,  20,  30,  40,  50,  60,  70,  80,
9963, 112, 2,   116, 21,  31,  41,  51,  63,  72,  82
];

DATA:
LOAD
     inter,   
     client,  
     contrat,
     SubField(Data, '_', -1) AS annee,
     SubField(Data, '_', 2) AS garantie,
     IF(SubField(Data, '_', 1) = 'prime', Value) AS prime
Resident DATA_TMP
Where SubField(Data, '_', 1) = 'prime';

Left Join(DATA)
LOAD
     inter,   
     client,  
     contrat,
     SubField(Data, '_', -1) AS annee,
     Value AS sinistre
Resident DATA_TMP
Where SubField(Data, '_', 1) = 'sinistre';

DROP Table DATA_TMP;

Result:

Regards,

Vitalii

[Ily]

Thank you so much , ureally solved my problem. Just another question. Could you please tell me how can i load years as dimension from the calendation (Just the last five years ) .

Thank you again

[vchuprina]

Hi, 

Add a filter to your calendar table:

Load 

...

 Year

From Calendar

Where Year >= Year(Today())-5;

Regards,

Vitalii

[Ily]

Hello,

I'm sorry, i'm beginner to Qliksense, but it didnt work for me 

Maybe, i misexplained, i want to create dimension year from AutoCalender limited to the last five years 

PS: The table Calendar doesn't exist.

[vchuprina]

Hi,

Calendar limited to the last five years:

MasterCalendar:
Load
 TempDate AS YourDate,
 week(TempDate) As Week,
 Year(TempDate) As Year,
 Month(TempDate) As Month,
 Day(TempDate) As Day,
 'Q' & ceil(month(TempDate) / 3) AS Quarter,
 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
 WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from today ===/
LOAD
 YearStart(Today(),-5) as mindate,
 today() as maxdate
AUTOGENERATE 1;

Based on your date field

//=== Get min/max dates from Field ===/
LOAD
 YearStart(max(FieldValue('YourDateFIeld', recno())), -5) as mindate,
 max(FieldValue('YourDateFIeld', recno())) as maxdate
AUTOGENERATE FieldValueCount('YourDateFIeld');

Also, please read this article 

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

Regards,

Vitalii