topic Load script Loop: Multiple lines to find most recent transaction in QlikView

Load script Loop: Multiple lines to find most recent transaction

Thu, 15 Oct 2015 12:50:37 GMT

Afternoon,

Thanks in advance of any help you share.

I have the below data and am looking to load only the most recent transaction by PolicyNumber so that I can drop/remove all previous data.

Example:

Table 1 (to be dropped)

PolicyNumber	Premium	Transaction Type	Transaction Data
PN000123	£500	NB	01/01/2015
PN000125	£800	NB	14/01/2015
PN000126	£450	NB	01/02/2015
PN000123	£92	ADJ	03/02/2015
PN000123	£0	ADJ	04/02/2015
PN000125	£72	ADJ	15/01/2015
PN000128	£475	NB	01/03/2015

I'm looking to reduce the above data load to this: (The most recent transaction by PolicyNumber)

Table 2

PolicyNumber	Premium	Transaction Type	Transaction Data
PN000126	£450	NB	01/02/2015
PN000123	£0	ADJ	04/02/2015
PN000125	£72	ADJ	15/01/2015
PN000128	£475	NB	01/03/2015

Thanks again

Gary

Re: Load script Loop: Multiple lines to find most recent transaction

jonathandienst — Thu, 15 Oct 2015 12:55:43 GMT

Use an inner join to exclude all but the most recent line for each policy:

Policies:

LOAD

PolicyNumber,

Premium,

[Transaction Type],

[Transaction Data]

FROM ...;

Inner Join (Policies)

LOAD PolicyNumber,

Max([Transaction Data]) As [Transaction Data]

Resident Policies;

Re: Load script Loop: Multiple lines to find most recent transaction

swuehl — Thu, 15 Oct 2015 13:35:17 GMT

If your data source allows ORDER BY:

Policies:

LOAD

PolicyNumber,

Premium,

[Transaction Type],

[Transaction Data]

FROM ...

WHERE NOT EXISTS(PolicyNumber)

ORDER BY PolicyNumber, [Transaction Data] DESC;

If you want to go for Jonathan's approach, I think you need to add a GROUP BY clause:

Policies:

LOAD

PolicyNumber,

Premium,

[Transaction Type],

[Transaction Data]

FROM ...;

Inner Join (Policies)

LOAD PolicyNumber,

Max([Transaction Data]) As [Transaction Data]

Resident Policies

GROUP BY PolicyNumber;

Re: Load script Loop: Multiple lines to find most recent transaction

Fri, 16 Oct 2015 06:45:48 GMT

Hi Swuehl , could you please explain us

WHERE NOT EXISTS(PolicyNumber)

ORDER BY PolicyNumber, [Transaction Data] DESC

what it will do ,

as per the Help I know that exists will check the previously loaded records. but here all the records are loading at a time , could you please explain me the flow of the statements you have provided.

Thanks

John

Re: Load script Loop: Multiple lines to find most recent transaction

swuehl — Fri, 16 Oct 2015 14:39:27 GMT

John,

the HELP says: "Determines whether a specific field value exists in a specified field of the data loaded so far."

Loaded so far in includes the data from the current LOAD statement, up to the previous record.

Above WHERE clause will only let pass the first occurence of PolicyNumber in the LOAD (if this is the first appearance in the script run). To fulfill the initial requirements, you need an additional ORDER BY ... DESC, to only retrieve the latest data.

Re: Load script Loop: Multiple lines to find most recent transaction

Fri, 16 Oct 2015 15:07:15 GMT

Afternoon all,

Inner Join (Policies)

LOAD PolicyNumber,

Max([Transaction Data]) As [Transaction Data]

Resident Policies

GROUP BY PolicyNumber;

The above statement works perfectly however I have now changed the Transaction date to a date and time field due to multiple transactions on the same day.

The max function does not appear to work correctly with a date and time field, could someone please advice?

Thanks

Gary

Re: Load script Loop: Multiple lines to find most recent transaction

swuehl — Fri, 16 Oct 2015 15:13:05 GMT

What do you mean with 'not appear to work correctly with a date and time field'?

It should work as designed, but you probably don't want to get the max Date and max Time calculcated independently, but the max Time on max Date, right? I think you would need to combine both values again to a DateTime value, maybe split the values up only later in the script.

edit:

or try something like

Inner Join (Policies)

LOAD PolicyNumber,

Max([Transaction Date]) As [Transaction Date],

frac(Max([Transaction Date]+[Transaction Time])) as [Transaction Time]

Resident Policies

GROUP BY PolicyNumber;