Is there a smarter way to transform data in data load editor?

[Anonymous]

Hi all,

I wanna develop an app regarding customer complaints.

I have a few questions I hope you can help me with.

Question:

Is there a smarter way of transforming multiple data with a "tag" into just 1 tag? - (wish the tag to just be "warranty")

In my excel spreadsheet I have many lines that looks like this in various combinations:

235-WTY-Returned to Stock

208-WTY-Repair and Return

And this list go on with 50+ more combinations.

Note that beside "BILL" there is also 50+ various of "BILL" and "SC".

In Qlik Sense Data load editor, I have been using the below script to transform the data:

IF([Warranty situation]='202-BILL-Exchange' or

[Warranty situation]='202-BILL-Loaner' or

[Warranty situation]='235-BILL-Returned to Stock', 'Warranty',

Like a simple code/script can transform anything that containts WTY into Warranty and BILL then Outside Warranty.

Thanks in advance !!

Best regards,
Henrik.

[robert99]

Use If and Wildmatch and  within wildmatch "*WTY*" (or '*WTY*')

IF(WILDMATCH(Warranty situation,"*WTY*")>0, ETC

[swuehl]

Maybe with one of the Match functions:

https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/Condition...

IF(Wildmatch([Warranty situation],'*BILL*'), 'Warranty')

[NZFei]

I suggest that you create a logical island table that lists all the Warranty types:

WarrantyTable:

Load * inline [

WarrantyType

BILL

SC

WTY

];

Then use the following script to group and flag the Warranty lines:

if(exists(WarrantyType, upper(subfield([Warranty situation],'-',2))), 'Warranty','Not Warranty') as WarrantyFlag

The good thing for this solution is that you only need to maintain the WarrantyTable if there is any new warranty types, or some types need to be removed.

Hope it can help.

Fei

[rwunderlich]

Expanding on Robert's suggestion a bit:

Pick( Match( [Warranty situation], 'WTY*, '*BILL*'), 'Warranty', 'Outside Warranty')

-Rob