Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I am pretty new to Qlikview and I am working on a Dashboard.
I have two tables loaded in Qlikview.
Table1 has the following columns: Date, org, invoice_count
Table2 only has dates (but less dates than the dates in Table1)
I am looking for a function that extends bable1 by another date column and it should pick the next date that is available in Table2 if the exact value cannot be found.
Let's assume the tables look like this:
Table 1
Date | Organization | Count |
---|---|---|
21-OCT-2016 | ABC | 5 |
22-OCT-2016 | ABC | 10 |
23-OCT-2016 | ABC | 8 |
24-OCT-2016 | ABC | 5 |
Table 2
Dates |
---|
20-OCT-2016 |
21-OCT-2016 |
24-OCT-2016 |
25-OCT-2016 |
So the extended Table 1 (or a new table should look like this:
Date | Organization | Count | New_date |
---|---|---|---|
21-OCT-2016 | ABC | 5 | 21-OCT-2016 |
22-OCT-2016 | ABC | 10 | 24-OCT-2016 |
23-OCT-2016 | ABC | 8 | 24-OCT-2016 |
24-OCT-2016 | ABC | 5 | 24-OCT-2016 |
Are you aware of any way that this could be achieved?
Thanks,
Benny
Try this out:
Table:
LOAD Date,
Organization,
Count
FROM
[https://community.qlik.com/thread/237718]
(html, codepage is 1252, embedded labels, table is @1);
Table2:
LOAD Dates
FROM
[https://community.qlik.com/thread/237718]
(html, codepage is 1252, embedded labels, table is @2);
Left Join(Table2)
LOAD Date(Min + IterNo() - 1) as NewDates
While Min + IterNo() - 1 <= Max;
LOAD Min(Dates) as Min,
Max(Dates) as Max
Resident Table2;
Left Join (Table)
LOAD Min(Dates) as NewDates,
NewDates as Date
Resident Table2
Where Dates >= NewDates
Group By NewDates;
DROP Table Table2;
X:
LOAD Date(Date#(Dates, 'DD-MMM-YYYY')) as Dates, rowno() as Id
FROM [https://community.qlik.com/thread/237718] (html, codepage is 1252, embedded labels, table is @2);
XMM:
LOAD
Date(MinD + IterNo() -1) as Date
While MinD + IterNo() -1 <= MaxD;
LOAD
(min(Dates)) as MinD,
(max(Dates)) as MaxD
Resident X;
Left Join (XMM)
load Dates as Date, Dates
Resident X;
M:
NoConcatenate load
Date,
if(len(trim(Dates)), Dates, Peek('Dates')) as Dates
Resident XMM
order by Date desc;
DROP Table XMM;
DROP Table X;
Y:
LOAD Date(Date#(Date, 'DD-MMM-YYYY')) as Date,
Organization,
Count
FROM
[https://community.qlik.com/thread/237718]
(html, codepage is 1252, embedded labels, table is @1);
Left Join (Y)
load Date, Dates as New_Date
Resident M;
DROP Table M;