- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Tags:
- data transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use If and Wildmatch and within wildmatch "*WTY*" (or '*WTY*')
IF(WILDMATCH(Warranty situation,"*WTY*")>0, ETC
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe with one of the Match functions:
IF(Wildmatch([Warranty situation],'*BILL*'), 'Warranty')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Expanding on Robert's suggestion a bit:
Pick( Match( [Warranty situation], 'WTY*, '*BILL*'), 'Warranty', 'Outside Warranty')
-Rob