Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
leale1997
Contributor III
Contributor III

Group field by content in QlikView Load statement

Hello,

I have a table with a field that has similar content in multiple rows.  I want to combine the multiple possibilities into one output.

This is the table: 

Status
Discontinued
N/A
Certified
Approved
Discontinued
Not Certified
N/A - non applicable

 

Thanks in advance for your assistance.

Labels (1)
1 Solution

Accepted Solutions
dplr-rn
Partner - Master III
Partner - Master III

Should combine it all conditions to same line
if(WildMatch(Certification_Status,'N/A*') or Certification_Status = 'Not Applicable' ,'N/A',Certification_Status) as Certification_Status ,

View solution in original post

7 Replies
leale1997
Contributor III
Contributor III
Author

The name of the field is 'Status'.  I would like to replace the varied 'N/A' and 'Not Applicable' and 'NA Non Site' indicators with something like 'N/A' so they all match.

dplr-rn
Partner - Master III
Partner - Master III

In you load script change status column like below
if(WildMatch(Status,'N/A*'),'N/A',Status) as Status
you will replace all occurrences or N/A* s with N/A
leale1997
Contributor III
Contributor III
Author

Thanks Dilipranjith,

It's kind of working.  I have another possible data instance that wasn't in the sample I posted.  

This is what I tried: 

if(WildMatch(Certification_Status,'N/A*'),'N/A',Status)as Status ,
if(Certification_Status = 'Not Applicable','N/A',Status)as Status,

The first statement is effective.  The second part is not.  What am i doing wrong here?

dplr-rn
Partner - Master III
Partner - Master III

Should combine it all conditions to same line
if(WildMatch(Certification_Status,'N/A*') or Certification_Status = 'Not Applicable' ,'N/A',Certification_Status) as Certification_Status ,
dplr-rn
Partner - Master III
Partner - Master III

Alternatively if you have a lot of these conditions use a map to set the values. makes for a cleaner code
e.g. something like below
Certification_Map:
mapping load inline [
Not Applicable, N/A
N/A,N/A
N/A Non Site, N/A
SOMEthing Else, Something
];

load....
applymap('Certification_Map',Certification_Status,Certification_Status) as Certification_Status,

rough code typed on mobile so please double check syntax
leale1997
Contributor III
Contributor III
Author

Thanks!  This solution worked.  

dplr-rn
Partner - Master III
Partner - Master III

no problem