Custom Filter

[ccifaldi]

Using script to create a custom filter. Filter applies correctly in transform layer then fails in load. What is happening is that we work with survey answers. I export the full set of answers from our data and then map responses to a simple version. For example, Canada, USA and Mexico map to North America for region. I do this in Excel. Then I import the Excel file and use the response to tie the new category to the unique response ID. Using a left join in transform to create a small subset table and then applying the table to main data in load. Probably not the most efficient way. Any suggestions? 

Transform script is:

Load script is: 

[sidhiq91]

@ccifaldi  Couple of things here:

1. Instead of mapping the Countries in Excel, you can perform that in the Qlik itself using a If() Statement.

2. I don't see anything wrong with the approach it seems to be ok.

If you are facing any issue in terms of performance or not getting desired output. You can post the sample data and I will try to help you out.

[ccifaldi]

Hi! Unfortunately I cannot use Qlik script to perform the mapping as my dataset includes not only every country in the world but various misspellings and other ways data has gotten into the system since we started collecting this data. If statements in Qlik script are capped. Also management does not like when it is done that way and prefers relational tables. What type of sample data do you need and I would be happy to provide it! Thank you for your help. 

[sidhiq91]

@ccifaldi You can provide me sample of data you are working on.

[ccifaldi]

I will. Working on large project and can get samples to you tomorrow. 

[ccifaldi]

Hello! I was finally able to prepare some sample data. I have attached an Excel. One tab is the relational table for the two region options. The other tab is how our data exports. I use a Crosstable function in Qlik so the final fields are Response ID, Question and Response. I think the system is getting confused because there are multiple questions (different text) that have the same regional answers. Trying to figure out how to "anchor" the filter to the headquarters column. Seems to work well within one set of data but I have multiple datasets and the headquarters question is worded a bit differently each time.