self-join to create measure

qliknexus_kgcru — Thu, 16 Aug 2018 09:37:16 GMT

I need to able to create measures based on the first 3 columns, i need to create 3 measures (AMOUNT_NEXTDATE2, AMOUNT_NEXTDATE3, AMOUNT_NEXTDATE4)

the AMOUNT_NEXTDATE2/3/4's value should be based on the next month

e.g. DATE_2 20160201 AMOUNT 10, value for AMOUNT_NEXT DATE2 should be 20 because the value for 20160301 is 20 which is the next month.

i tried doing a self-join but it doesn't work well for me. Any help? thanks!

DATE_1	DATE_2	AMOUNT	*AMOUNT_NEXT DATE2*	AMOUNT_NEXT DATE3	*AMOUNT_NEXT DATE4*
20170801	20160201	10	20	30	40
20170801	20160301	20	30	40	50
20170801	20160401	30	40	50	60
20170801	20160501	40	50	60	70
20170801	20160601	50	60	70	80
20170801	20160701	60	70	80	90
20170801	20160801	70	80	90	100
20170801	20160901	80	90	100	110
20170801	20161001	90	100	110	120
20170801	20161101	100	110	120	130
20170801	20161201	110	120	130	140
20170801	20170101	120	130	140	150
20170801	20170201	130	140	150	160
20170801	20170301	140	150	160	170
20170801	20170401	150	160	170	180

Re: self-join to create measure

Saravanan_Desingh — Wed, 29 Apr 2020 23:41:19 GMT

One solution is:

tab1: LOAD DATE_1, DATE_2, AMOUNT, DATE_1&'@'&DATE_2 As Key1, DATE_1&'@'&Date(AddMonths(Date#(DATE_2,'YYYYMMDD'),1),'YYYYMMDD') As Key2, DATE_1&'@'&Date(AddMonths(Date#(DATE_2,'YYYYMMDD'),2),'YYYYMMDD') As Key3, DATE_1&'@'&Date(AddMonths(Date#(DATE_2,'YYYYMMDD'),3),'YYYYMMDD') As Key4 FROM [C:\Users\sarav\Downloads\TEST DATA.xlsx] (ooxml, embedded labels, table is Sheet1); Left Join(tab1) LOAD Key1 As Key2, AMOUNT As [AMOUNT_NEXT DATE2] Resident tab1; Left Join(tab1) LOAD Key1 As Key3, AMOUNT As [AMOUNT_NEXT DATE3] Resident tab1; Left Join(tab1) LOAD Key1 As Key4, AMOUNT As [AMOUNT_NEXT DATE4] Resident tab1; Drop Field Key1, Key2, Key3, Key4;

topic Re: self-join to create measure in QlikView

self-join to create measure

Re: self-join to create measure