Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

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

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

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!

Labels (1)
4 Solutions

Accepted Solutions
jwjackso
Specialist II
Specialist II

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

Capture.PNG

View solution in original post

stevejoyce
Creator III
Creator III

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;

View solution in original post

Kushal_Chawda

@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);

View solution in original post

PaReD_SF85
Contributor III
Contributor III
Author

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

View solution in original post

6 Replies
jwjackso
Specialist II
Specialist II

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

Capture.PNG

View solution in original post

stevejoyce
Creator III
Creator III

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;

View solution in original post

Kushal_Chawda

@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);

View solution in original post

PaReD_SF85
Contributor III
Contributor III
Author

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

View solution in original post

PaReD_SF85
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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