Group by conflicts

[prathipsrinivas]

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

[dplr-rn]

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.

[rubenmarin]

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.

[prathipsrinivas]

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

[prathipsrinivas]

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.

[rubenmarin]

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;

[Peter_Cammaert]

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...

[prathipsrinivas]

Thanks a Lot Ruben,

Maxstring seems to be working.