topic Re: Max of Max in Script in QlikView

Max of Max in Script

prathipsrinivas — Mon, 22 Oct 2018 13:24:21 GMT

Hi All,

I would like to figure out maxstring(TransNo) in a Max(Entrydate) for a particular claim in Script.

Please suggest me how to implement it.

The idea is to set flag when When max of transactionNo and max entrydate record comes.

Thanks,

Prathip

Re: Max of Max in Script

sunny_talwar — Mon, 22 Oct 2018 13:31:10 GMT

So, each Entrydate can have multiple TransNo and you only want to flag the Max TransNo from the Max Entrydate?

EntryDate TransNo

10/15/2018 1

10/15/2018 2

10/15/2018 3

10/16/2018 2

10/16/2018 3

In the above data, you would want to only flag the last line?

Re: Max of Max in Script

prathipsrinivas — Mon, 22 Oct 2018 13:49:39 GMT

Exactly Sunny, and in Addition if there is currency field there, we would have to do the same for separate Currencies.

Like:

EntryDate TransNo Currency flag

10/15/2018 1 A

10/15/2018 2 A

10/15/2018 3 A 1

10/16/2018 2 B

10/16/2018 3 B 1

The result should be like mentioned above.

Re: Max of Max in Script

sunny_talwar — Mon, 22 Oct 2018 13:58:06 GMT

May be something like this

Table:

LOAD EntryDate,

TransNo,

Currency

FROM ....;

Left Join (Table)

LOAD Currency,

Max(EntryDate) as EntryDate,

FirstSortedValue(TransNo, -(EntryDate + TransNo)) as Recent

Resident Table;

FinalTable:

LOAD EntryDate,

TransNo,

Currency,

If(Recent = TransNo, 1) as flag

Resident Table;

DROP Table Table;

Re: Max of Max in Script

prathipsrinivas — Mon, 22 Oct 2018 14:09:33 GMT

Thanks Sunny but the catch here is transaction number is in mixed format, sometimes it is numbers and sometimes it is Alphanumeric.

I am not Sure whether the above approach would be suitable in this case.

Let me know if am wrong.

Thanks.

Re: Max of Max in Script

sunny_talwar — Mon, 22 Oct 2018 14:17:05 GMT

So, in that case, may be this

Table:

LOAD EntryDate,

TransNo,

Currency

FROM ....;

Left Join (Table)

LOAD Currency,

Max(EntryDate) as EntryDate,

FirstSortedValue(TransNo, -(EntryDate - Rank(TransNo))) as Recent

Resident Table;

FinalTable:

LOAD EntryDate,

TransNo,

Currency,

If(Recent = TransNo, 1) as flag

Resident Table;

DROP Table Table;

Re: Max of Max in Script

prathipsrinivas — Mon, 22 Oct 2018 14:59:02 GMT

I will try and let me know how it goes, Thanks.

Re: Max of Max in Script

prathipsrinivas — Mon, 22 Oct 2018 14:59:18 GMT

Sorry, Let you Know*