Load where date is higher

[#1]

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.

[vishsaggi · Accepted solution]

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.

[martynlloyd]

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]

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]

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;

[#6]

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.