Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a quick qlikview 12 question, that I just can't seem to get my head around to figure out.
I have 3 databases WEB, IVR, SMS and none of these databases speak to each other 😞 they are used to make applications and everyone who is eligible to make an application is assigned a unique PIN number. now since non of these databases speak to each other it is possible for someone to apply over all 3.
All I am needing to do is load the application with the highest created date, as per bellow example.
IVR:
Pin No Application date
1 14/10/16
2 15/10/16
3 16/10/16
WEB:
Pin No Application date
1 15/10/16
2 16/10/16
5 16/10/16
SMS:
Pin No Application date
2 17/10/16
6 15/10/16
7 16/10/16
So In this example i would expect PIN 1 to load the 15/10/16 date, PIN 2 to load 17/10/16 PIN 3 16/10/16 PIN 5 16/10/16 PIN 6 15/10/16 and PIN 7 16/10/16
There are more datebases that this all ties in to and more information that I am pulling and manipulating but I am hoping this can be independent from everything else.
Try this
IVR:
LOAD * INLINE [
PinNo, Applicationdate
1, 14/10/16
2, 15/10/16
3, 16/10/16
];
WEB:
LOAD * INLINE [
PinNo, Applicationdate
1, 15/10/16
2, 16/10/16
5, 16/10/16
];
SMS:
LOAD * INLINE [
PinNo, Applicationdate
2, 17/10/16
6, 15/10/16
7, 16/10/16
];
NoConcatenate
FINAL:
LOAD DISTINCT PinNo, Date(Max(Applicationdate)) AS ApplicationDate
Resident IVR
GROUP BY PinNo;
DROP TABLE IVR;
Then use a table box and add PinNo and ApplicationDate.
Try this
IVR:
LOAD * INLINE [
PinNo, Applicationdate
1, 14/10/16
2, 15/10/16
3, 16/10/16
];
WEB:
LOAD * INLINE [
PinNo, Applicationdate
1, 15/10/16
2, 16/10/16
5, 16/10/16
];
SMS:
LOAD * INLINE [
PinNo, Applicationdate
2, 17/10/16
6, 15/10/16
7, 16/10/16
];
NoConcatenate
FINAL:
LOAD DISTINCT PinNo, Date(Max(Applicationdate)) AS ApplicationDate
Resident IVR
GROUP BY PinNo;
DROP TABLE IVR;
Then use a table box and add PinNo and ApplicationDate.
Adam may not know about auto concatenation, better to be explicit:
Apps:
//IVR
LOAD
'IVR' as Application,
PinNo,
Applicationdate
from...
Concatenate
//WEB:
LOAD
'WEB' as Application,
PinNo,
Applicationdate
from...
Concatenate
//SMS:
LOAD
'SMS' as Application,
PinNo,
Applicationdate
from...
NoConcatenate
FINAL:
LOAD DISTINCT PinNo, Date(Max(Applicationdate)) AS ApplicationDate
Resident Apps
GROUP BY PinNo;
DROP TABLE Apps;
Regards,
Marty.
May be he knows, if not, lets explain like
Adam: IF tables has same number of fields and field names Qlikview concatenates the tables automatically.
You use forced concatenation if you want to merge the table rows with different fields and field names.
Hope this helps.
Another option could be
Table:
LOAD * INLINE [
PinNo, Applicationdate
1, 14/10/16
2, 15/10/16
3, 16/10/16
];
LOAD * INLINE [
PinNo, Applicationdate
1, 15/10/16
2, 16/10/16
5, 16/10/16
];
LOAD * INLINE [
PinNo, Applicationdate
2, 17/10/16
6, 15/10/16
7, 16/10/16
];
tmp:
Right Keep (Table)
LOAD
PinNo,
Date(Max(Applicationdate)) as Applicationdate
Resident Table
Group By PinNo;
DROP Table tmp;
Hi,
Thanks for this nice and simple with the concatenate the files all have different names and usually values, and the data is manipulated many times within the load script for varies different reports, as such I found this a nice simple solution.