topic Re: Can anyone help with this script in QlikView

Can anyone help with this script

Fri, 27 Dec 2013 12:12:34 GMT

Hello,

I have a problem where I need find if the max date is I have for a trade was on Sunday and if so the deduct 2 days worth of fees. i have tried the below code on load but keep invalid expression. Is this something that can be done?

Load *,
If(WeekDay(Max(MyDate))= 'Sun', Amount1 *((0.2/10000)/365), (Amount1 *((0.2/10000)/365))*-2 ) as Quantity3
Resident tblSecondLoad

Order by CrossRef, MyDate desc;

drop table tblSecondLoad;

Regards

Alan

Re: Can anyone help with this script

simondachstr — Fri, 27 Dec 2013 12:19:34 GMT

Weekday() returns an integer between 1-6. In your case, simply replace 'Sun' with '6'. If that doesn't work either, make sure MyDate has a correct Date format and is not a string.

Re: Can anyone help with this script

Peter_Cammaert — Fri, 27 Dec 2013 12:32:41 GMT

You cannot use the Max() function in a LOAD statement that has no GROUP BY clause (only one exception: use only aggregation functions to create single record with statistics from a full table - see Master Calendar examples)

One solution is to group your records by a "Trade" identifier and then extract the max date. Join the max date back into the table to decide where to subtract 2 days worth of fees.

Peter

Re: Can anyone help with this script

Fri, 27 Dec 2013 12:49:04 GMT

Thanks all,

I tried the following but it still doesnt work and also tried to change from Sun to 6 just getting the same error.

Load *,

If(WeekDay(Max(MyDate))= 'Sun', Amount1 *((0.2/10000)/365), (Amount1 *((0.2/10000)/365))*-2 ) as Quantity3

Resident tblSecondLoad

Group by CrossRef;

drop table tblSecondLoad;

Re: Can anyone help with this script

sivarajs — Fri, 27 Dec 2013 13:01:48 GMT

Your loading all the fields in load statement (Load *). In this case you need all the fields in your groupby statement. So try without *, load as separate table

Re: Can anyone help with this script

Peter_Cammaert — Fri, 27 Dec 2013 13:20:05 GMT

The story behind GROUP BY:

GROUP BY aggregates records, i.e. reduces the number of records to a series of summaries.
Only fields specified in the GROUP BY clause can be listed in the LOAD expressions as-is or in expressions without aggregation functions.
All fields specified in the GROUP BY clause should be used somewhere in the LOAD list.
All other fields not specified in the GROUP BY clause can only be listed as LOAD fields if they are used inside aggregation functions (i.e. Max, Min, Count, Sum etc.)

Perform a two step approach:

First for every "Trade", extract the max date (use a GROUP BY clause to load records in a new table). Then JOIN the max date back into the original table based on "Trade" identifier (or even better, create a mapping table and use applymap() to translate every "Trade" ID into a max date value)
Now step through your original table, and decide on where to subtract 2 days worth of fees based on the max date field included in every record. This one can be done sequentially, without GROUP BY.

Best,

Peter

Re: Can anyone help with this script

mjm — Fri, 27 Dec 2013 14:22:18 GMT

have you tried a modification of your script as follows:

Load
If(WeekDay(MyDate)= 'Sun', Amount1 *((0.2/10000)/365), (Amount1 *((0.2/10000)/365))*-2 ) as Quantity3;

Resident tblSecondLoad;

drop table tblSecondLoad;

Please mark this answer as correct and complete if this helps.

Thanks