topic Re: Load where date is higher in QlikView

Load where date is higher

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Load where date is higher

vishsaggi — Fri, 27 Jan 2017 16:46:53 GMT

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.

Re: Load where date is higher

martynlloyd — Fri, 27 Jan 2017 17:13:28 GMT

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.

Re: Load where date is higher

vishsaggi — Fri, 27 Jan 2017 17:20:32 GMT

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.

Re: Load where date is higher

maxgro — Fri, 27 Jan 2017 18:16:24 GMT

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;

Re: Load where date is higher

Tue, 31 Jan 2017 11:13:22 GMT

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.