topic Re: Join tables & Add Amount for a Date in QlikView

Solved

microwin88x — Wed, 25 Nov 2015 18:06:00 GMT

Solved

Re: Join tables & Add Amount for a Date

sunny_talwar — Wed, 25 Nov 2015 18:49:09 GMT

May be this:

TABLE_2:

LOAD DATE_2,

ID_PROVIDER,

ZONE

FROM

[https://community.qlik.com/thread/193014]

(html, codepage is 1252, embedded labels, table is @2);

Table:

LOAD * INLINE [

DATE_1, ID_PROVIDER, AMOUNT

01/01/2015, 20, 10

01/01/2015, 50, 85

01/01/2015, 30, 25

04/01/2015, 20, 21

02/01/2015, 30, 44

03/01/2015, 50, 25

05/01/2015, 20, 46

06/01/2015, 50, 87

08/01/2015, 30, 47

01/01/2015, 70, 100

];

Join(Table)

LOAD ID_PROVIDER,

Date(MinDate + IterNo() - 1) as DATE_1

While MinDate + IterNo() - 1 <= MaxDate;

LOAD ID_PROVIDER,

Max(DATE_2) as MaxDate,

Min(DATE_2) as MinDate

Resident TABLE_2

Group By ID_PROVIDER;

FinalTable:

NoConcatenate

LOAD DATE_1,

ID_PROVIDER,

If(Len(Trim(AMOUNT)) = 0, Peek('AMOUNT'), AMOUNT) as AMOUNT

Resident Table

Order By ID_PROVIDER, DATE_1;

Left Join (TABLE_2)

LOAD DATE_1 as DATE_2,

ID_PROVIDER,

AMOUNT as LAST_AMOUNT

Resident FinalTable;

DROP Tables Table, FinalTable;

Re: Join tables & Add Amount for a Date

MarcoWedel — Thu, 26 Nov 2015 01:09:13 GMT

Hi,

another solution could be:

(different from your expected result because of the year-typo in your TABLE_1)

TABLE_1:
LOAD * FROM [https://community.qlik.com/thread/193014] (html, codepage is 1252, embedded labels, table is @1);

TABLE_2:
LOAD * FROM [https://community.qlik.com/thread/193014] (html, codepage is 1252, embedded labels, table is @2);

tabTemp:
LOAD DATE_1, ID_PROVIDER Resident TABLE_1;
Join
LOAD DATE_2, ID_PROVIDER Resident TABLE_2;
Right Join
LOAD *, AutoNumberHash128(DATE_1,DATE_2) as %DateKey;
LOAD Max(DATE_1) as DATE_1, DATE_2, ID_PROVIDER
Resident tabTemp
Where DATE_1<=DATE_2
Group By DATE_2, ID_PROVIDER;

Left Join (TABLE_1) LOAD DATE_1, ID_PROVIDER, %DateKey Resident tabTemp;
Left Join (TABLE_2) LOAD DATE_2, ID_PROVIDER, %DateKey Resident tabTemp;

DROP Table tabTemp;

hope this helps

regards

Marco

Re: Join tables & Add Amount for a Date

tripatirao — Thu, 26 Nov 2015 13:23:29 GMT

Hi Sunny

Got so much basic concept form this problem.

Thanks a lot.