Qliksense Script New Fıeld

[krmvacar]

Hi All,

I have a table like below and script to this table
I want to add a "NEWRESULT" field.

I want to write the largest sum based on DATE ,ID and PRODUCTID and write zero for the rest.

How can I do this in the script part, is it possible qliksense?

Your help is welcome

Date	ID	PRODUCTID	RESULT	NEWRESULT
03.01.2022	17027	E505HK02	14,51	14,51
03.01.2022	17027	E505HK03	13,96	0
03.01.2022	17027	E505HK01	9,51	0
04.01.2022	17027	E505HK03	11,15	11,15
04.01.2022	17027	E505HK02	8,94	0
04.01.2022	17027	E505HK01	8,14	0
05.01.2022	17027	E505HK03	10,67	10,67
05.01.2022	17027	E505HK02	9,10	0
05.01.2022	17027	E505HK01	8,30	0

[Vegar]

Maybe like this?

Result:

Load Date, ID, PRODUCTID, RESULT From Source;

Left Join Date, ID, PRODUCTID, max(RESULT) as RESULT, Max(RESULT) AS NewResult

Resident Result

group by Date, ID, PRODUCTID;

[krmvacar]

Hi @Vegar ,

First of all thank you for helping.

I tried as you said but unfortunately it didn't work.

Can we put a flag on the highest one? Then we can only get flag ones?

Best Regards,

Result:
load * Inline [
Date , ID ,PRODUCTID ,RESULT
03.01.2022 ,17027 ,E505HK02 ,14,51
03.01.2022 ,17027 ,E505HK03 ,13,96
03.01.2022 ,17027 ,E505HK01 ,9,51
04.01.2022 ,17027 ,E505HK03 ,11,15
04.01.2022 ,17027 ,E505HK02 ,8,94
04.01.2022 ,17027 ,E505HK01 ,8,14
05.01.2022 ,17027 ,E505HK03 ,10,67
05.01.2022 ,17027 ,E505HK02 ,9,10
05.01.2022 ,17027 ,E505HK01 ,8,30
];

Left Join(Result)
load
Date,
ID,
PRODUCTID,
Max(RESULT) AS RESULT,
Max(RESULT) AS NewResult
Resident Result
Group by Date, ID, PRODUCTID;

[Vegar]

If you want to just flag then replace

Max(RESULT) AS NewResult

with

1 as FlaggedResult

You wrote "it didn't work". What didn't work? What did you get instead of the expected output?

PS: Your inline will not work because your column separator(,) is equalizer to your decimal separator (,). 

[MayilVahanan · Accepted solution]

HI

It looks like, New Result is based on Date & ID. not by Date, ID & Product ID. Because, if its goes combination of 3 field, all the rows has value.

Hope my understanding is correct. Try to remove ProductId from the Vegar result and try it once.

Load Date, ID, PRODUCTID, RESULT From Source;

Left Join Date, ID, max(RESULT) as RESULT, Max(RESULT) AS NewResult

Resident Result

group by Date, ID;

[krmvacar]

Hi @Vegar 

If I write it like this, the result is as follows:

Left Join(Result)
load
Date,
ID,
PRODUCTID,
Max(RESULT) AS RESULT,
Max(RESULT) AS NewResult
Resident Result
Group by Date, ID, PRODUCTID;

if i add flag as you wrote:

Left Join(Result)
load
Date,
ID,
PRODUCTID,
1 as FlaggedResult,
Max(RESULT) AS NewResult
Resident Result
Group by Date, ID, PRODUCTID;

am i making a mistake somewhere?

[krmvacar]

Yes ıts workıng thank you so much.