Solved

[microwin88x]

Solved

[sunny_talwar · Accepted solution]

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;

[MarcoWedel]

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

[tripatirao]

Hi Sunny

Got so much  basic concept  form this problem.

Thanks a lot.