topic Re: Group by conflicts in QlikView

Group by conflicts

prathipsrinivas — Tue, 16 Oct 2018 12:53:42 GMT

Hi All,

I'm trying to find the value from the field where date field has maximum value which I got easily using Group by function in the script.

And now, for the same max date there are 3 different values (alphanumeric) are present out of which last value I want and set a flag against that value.

I am finding difficulties in achieving it.

It would be helpful if someone throw lights on it .

Thank,

Prathip

Re: Group by conflicts

dplr-rn — Tue, 16 Oct 2018 13:10:03 GMT

can you elaborate on below. share your script sample output etc.

And now, for the same max date there are 3 different values (alphanumeric) are present out of which last value I want and set a flag against that value.

Re: Group by conflicts

rubenmarin — Tue, 16 Oct 2018 13:29:46 GMT

Hi Prathip, it depends on how the data is sorted, if the last value is also the last value sorted alphabetically you can use MaxString(), in other case an easy way can be adding a RowNo() field and use this field to sort the table descending to retrieve the first record.

Re: Group by conflicts

prathipsrinivas — Tue, 16 Oct 2018 13:33:29 GMT

Yeah Sure,

Let's take an example

I have a max Date of Purchase: 12/10/2018

in this date three transactions happened, whose TransactionKeys are

we12345 67ty

er34523 788hf

tr55678 453tr

here, i want last transactionkey. same way for all the purchases.

Table looks something like this

purchase date transactionkey

1 12/10/2018 we12345 67ty

1 12/10/2018 er34523 788hf

1 12/10/2018 tr55678 453tr

Re: Group by conflicts

prathipsrinivas — Tue, 16 Oct 2018 13:36:01 GMT

Thanks for your idea.

Does Maxstring() works for alphanumeric fields?

And using RowNo(), can we use group by?

Very much Appreciated if you share any sample qvw.

Thanks.

Re: Group by conflicts

rubenmarin — Tue, 16 Oct 2018 13:45:43 GMT

Yes, Maxstring sorts alphanumeric, and Rowno can be used with group by.

To keep the last flagged record you can do a join with the max row number of the flagged records, like:

data:

LOAD*Inline [

purchase,date,transactionkey

1,12/10/2018,we12345 67ty

1,12/10/2018,er34523 788hf

1,12/10/2018,tr55678 453tr

];

Sorted:

LOAD *, RowNo() as row Resident data;

Inner Join LOAD max(row) as row

// Where maxflag=1

Resident Sorted;

drop table data;

Re: Group by conflicts

Peter_Cammaert — Tue, 16 Oct 2018 13:50:28 GMT

Or another technique could be used if you have a time value in your Transaction infomation. Using FirsSortedValue() can lift out all field values that belong to the last transaction.

LEFT JOIN(OriginalTransactions)

LOAD Date,

FirstSortedValue(TransactionKey, -Time) AS TransactionKey, // Max(Time) as key also ok

True() AS ThisIsTheLastOne

FROM Source (Options)

GROUP BY Date;

There is only a small chance that you'll find time values in your table, but I'm just trying to make clear that you have to find a strict rule for assigning a transaction the status "Last". Load order or Display order may not work well...

Re: Group by conflicts

prathipsrinivas — Tue, 16 Oct 2018 14:01:39 GMT

Thanks a Lot Ruben,

Maxstring seems to be working.