Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Hi,
Add a filter to your calendar table:
Load
...
Year
From Calendar
Where Year >= Year(Today())-5;
Regards,
Vitalii
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.
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