Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load where date is higher

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.

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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. Capture.PNG

View solution in original post

5 Replies
vishsaggi
Champion III
Champion III

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. Capture.PNG

martynlloyd
Partner - Creator III
Partner - Creator III

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.

vishsaggi
Champion III
Champion III

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.

maxgro
MVP
MVP

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;

Not applicable
Author

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.