Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
OJT
Contributor II
Contributor II

Splitting a field into new fields

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. 

clipboard_image_0.png

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! 

Labels (5)
5 Replies
Vegar
MVP
MVP

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
OJT
Contributor II
Contributor II
Author

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 

Anil_Babu_Samineni

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];

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Vegar
MVP
MVP

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

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
OJT
Contributor II
Contributor II
Author

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