Skip to main content
Announcements
Global Transformation Awards submissions are open! SUBMIT YOUR STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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.

4 Replies
robert99
Specialist III

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

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

swuehl
MVP

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
Partner - Specialist

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
Partner Ambassador/MVP

Expanding on Robert's suggestion a bit:

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


-Rob