Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have loaded a field [Spend rating] with values 10,9,8,7,6,5,4,3,2,1 and another field containing the spend for each rating [Total Spend Labour & Parts (£)] into qlik load editor.
I need to combine the spend of 10 and 9, 8 and 7, 6 and 5, 4 and 3, 2 and 1 to create 5 new fields.
I can't figure out how do it this in the load editor...please help!
What do you medan by 5 new fields? I new field with 5 values?
Div(SpendRating, 2)+1 as SpendRatingGroup
Or five new fields with flag as values?
If(match(SpendRating, 1,2), 1) as SpendRatingGroup1,
If(match(SpendRating, 3,4), 1) as SpendRatingGroup2,
Etc...
I need 1 field to be the SUM spend for Spend rating 10 and 9.
Then another field to be the SUM spend for Spend rating 8 and 9.
Etc...I hope this helps
Perhaps this?
T1:
Load [Spend Rating],
[SPV Rating],
[£SPV (Total)],
[Total Spend Labour & Parts (£)]
From Table;
Left Join (T1)
Load [Spend Rating],
If(Match([Spend Rating], '10', '9'), Sum([Total Spend Labour & Parts (£)])) as [10-9 Field],
If(Match([Spend Rating], '8', '7'), Sum([Total Spend Labour & Parts (£)])) as [8-7 Field],
If(Match([Spend Rating], '6', '5'), Sum([Total Spend Labour & Parts (£)])) as [6-5 Field],
If(Match([Spend Rating], '4', '3'), Sum([Total Spend Labour & Parts (£)])) as [4-3 Field],
If(Match([Spend Rating], '2', '1'), Sum([Total Spend Labour & Parts (£)])) as [2-1 Field]
Resident T1 Group By [Spend Rating];
If you can accept to do the aggregation in the application objects then you can do like this.
LOAD
If(match(SpendRating, 1,2), [Total Spend Labour & Parts (£)]) as [Spend rating 1 and 2],
If(match(SpendRating, 3,4), [Total Spend Labour & Parts (£)]) as [Spend rating 3 and 4],
If(match(SpendRating, 5,6), [Total Spend Labour & Parts (£)]) as [Spend rating 5 and 6],
If(match(SpendRating, 7,8), [Total Spend Labour & Parts (£)]) as [Spend rating 7 and 8],
If(match(SpendRating, 9,10), [Total Spend Labour & Parts (£)]) as [Spend rating 9 and 10],
...
FROM ...
Hi,
Thank you for the reply but this isn't quite there.
When i load that script the newly created fields display the Sum of the separate spend ratings.
My intention is to simply combine, for example, 10 and 9 spend rating under one field name (Category 5). The spend of each customer under 10 and 9 should also be under the Category 5 field name.
Any help?
Regards,
Oscar