Count multiple field values to assign value to a new field during load

[PaReD_SF85]

I am not sure how to code the following in my load script.  Any advice is appreciated!

I have 6 columns (fields) to evaluate for each Topic. Each has either an X or is blank.  Sample of data to visualize what I need:

Topic	Char1	Char2	Char3	Char4	Char5	Char6	Category (to be assigned via script)
Abc		X		X		X	Multi
Def	X						Char1
Ghi		X	X	X	X		Multi
Jkl		X					Char 2
Mno	X		X	X			Multi

I need to count the X’s in the Char fields. If the sum/count of Char1+Char2+Char3+Char4+Char5+Char6 >1, ‘Multi’ is the Category field value assigned/loaded. If the count is 1, I need the field name with the X as the value in Category.

Thank you!

[jwjackso · Accepted solution]

Data:
Load *,
If(SubStringCount(Char1&Char2&Char3&Char4&Char5&Char6,'X') > 1,'Multi',Pick(Index(Char1&Char2&Char3&Char4&Char5&Char6,'X'),'Char1','Char2','Char3','Char4','Char5','Char6')) as Category;
load * Inline [
Topic,Char1,Char2,Char3,Char4,Char5,Char6
Abc,' ',X,' ',X,' ',X
Def,X,' ',' ',' ',' ',' '
Ghi,' ',X,X,X,X,' '
Jkl,' ',X,' ',' ',' ',' '
Mno,X,' ',X,X,' ',' '
];

[stevejoyce · Accepted solution]

is it always 6 fields (Char1-6).  If it's dynamic we can update below to be dynamic.

This to me is the best way of handling it.  Have a look below.  you can exit script between steps if you want to see what is going on + comments in there.

data:
LOAD
*
from https://community.qlik.com/t5/QlikView-App-Dev/Count-multiple-field-values-to-assign-value-to-a-new-...

(html, utf8, embedded labels, table is @1)
;

drop field [Category (to be assigned via script)];

//Unpivot Char fields
work_crosstable:
crosstable (Char, Data)
load *
resident data;

//Count number of records with X for each Topic
work_countflags:
Load
Topic
,count(Data) as NoOfFlags
resident work_crosstable
where len(Data)>0
group by Topic;

//Join flag count back to cross table
right join (work_crosstable)
load
Topic
,NoOfFlags
resident work_countflags;

drop table work_countflags;

//Label flags as needed and join back to data table
left join (data)
load Distinct
Topic
,if(NoOfFlags = 0, 'None', If(NoOfFlags = 1, Char, If(NoOfFlags>1, 'Multi'))) as Category
resident work_crosstable
where len(Data) > 0;

drop table work_crosstable;

exit script;

[Kushal_Chawda · Accepted solution]

@PaReD_SF85  another way

Map_field:
mapping LOAD * Inline [
Pattern, Value
100000,Char1
010000,Char2
001000,Char3
000100,Char4
000010,Char5
000001,Char6 ];

Data:
LOAD Topic, 
     ApplyMap('Map_field',SubStringCount(lower(trim(Char1)),'x')&
     SubStringCount(lower(trim(Char2)),'x')&
     SubStringCount(lower(trim(Char3)),'x')&
     SubStringCount(lower(trim(Char4)),'x')&
     SubStringCount(lower(trim(Char5)),'x')&     
     SubStringCount(lower(trim(Char6)),'x'),'Multi') as Category,
     Char1, 
     Char2, 
     Char3, 
     Char4, 
     Char5, 
     Char6
FROM
[https://community.qlik.com/t5/QlikView-App-Dev/Count-multiple-field-values-to-assign-value-to-a-new-field/td-p/1834768]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

[PaReD_SF85 · Accepted solution]

Thank you for the prompt response, jwjackso!  I was not familiar with SubStringCount. 

[PaReD_SF85]

Thank you Kushal!  This makes sense to me as well.  I was not aware of SubStringCount before this.  (The key to  resolving this scenario!)

[PaReD_SF85]

Thank you stevejoyce!  It could grow to more than 6 columns to evaluate over time.  For my learning, I am going to  'play' with your option as well as the others submitted.  Love that QlikView has multiple ways to approach a solution (once you know the key function to use, etc.)  😊