topic Group field by content in QlikView Load statement in QlikView

Group field by content in QlikView Load statement

leale1997 — Sat, 16 Nov 2024 03:23:36 GMT

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.

Re: Group field by content in QlikView Load statement

leale1997 — Tue, 04 Jun 2019 17:13:15 GMT

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.

Re: Group field by content in QlikView Load statement

dplr-rn — Tue, 04 Jun 2019 17:23:32 GMT

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

Re: Group field by content in QlikView Load statement

leale1997 — Tue, 04 Jun 2019 17:37:42 GMT

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?

Re: Group field by content in QlikView Load statement

dplr-rn — Tue, 04 Jun 2019 17:41:07 GMT

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 ,

Re: Group field by content in QlikView Load statement

dplr-rn — Tue, 04 Jun 2019 17:46:41 GMT

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

Re: Group field by content in QlikView Load statement

leale1997 — Tue, 04 Jun 2019 17:48:05 GMT

Thanks! This solution worked.

Re: Group field by content in QlikView Load statement

dplr-rn — Tue, 04 Jun 2019 17:48:59 GMT

no problem