Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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;
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 (,).
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;
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?
Yes ıts workıng thank you so much.