add a filter at load script

[coloful_architect]

Hi guys,

I want to load fields of Product and Competitor Flag into Qlik sense.

But at competitor flag, I would love to change some products' flag data. At this example, wanna change product A, B, C into No rather than Yes.

Is there a way at loading script to achieve this rather than changing them at raw data file? 

Product	Competitor Flag	New Competitor Flag
A	Yes	No
B	Yes	No
C	Yes	No
D	Yes	Yes
E	Yes	Yes
F	Yes	Yes
G	No	No
H	No	No
I	Yes	Yes
J	No	No

[ArnadoSandoval]

Hi @coloful_architect 

You did not elaborate on the reasons to change a field (Competitor Flag) in the load script? in general we load data from legacy systems without transforming it, because Qlik's models represent a view of the sourced data, your request breaks that rule. If we know more about the reasons to do that we could be able to share some ideas with you.

hth

[MayilVahanan]

Hi @coloful_architect 

You can change it in Qlik, if its ur requirement, then try like below

If(Match(Product, 'A', 'B', 'C'), 'No', [Competitor Flag]) as [New Competitor Flag]

[coloful_architect]

Thanks Arnado for your thoughts.

The reason behind is that the original data from legacy system are wrong for some reasons. In this case, there is about a dozen of products with wrong competitor flag. I have built a few sheets based on it so there are various line ,pie , table charts and panel filters attached and relevant associate other fields are linked. Rather than changing expressions one by one for each fields at each sheet for each chart, I am seeking if we have a good solution to edit data at loading script. 

Since we have where statement which is able to do some filter work at loading script, wondering if we have similar logic way to solve this problem too.

[coloful_architect]

Hi Mayli, 

do you mean to edit expression at Analysis mode for dashboard as opposed to load script?

I have bunch of tables and charts created with this flag field, feeling it would take a lot of work to edit each of them.

anyway we could do at load script mode? 

Thanks

[ArnadoSandoval]

Hi @coloful_architect 

Now, I understand your issue, perhaps the best approach if for the business to fix the data at its source; if this is not possible, then I suggest you to introduce an Excel file with two columns; as the screenshot illustrate:

The in your Load Script, you implementing a mapping logic to this data, with something like this:

Map_CompetitionFlag:
Mapping Load
     Product,
     New_Competition_Flag
From ... <your Excel file location>;        

 Then, later in the Load Script, when you load the compromised data you do:

YourData:
Load 
     Product,
     ApplyMap('Map_CompetitionFlag', Product, [Competitor Flag]) As [Competitor Flag],
     ...
From <your data>;

This solution is scalable because if in the future, another product has an invalid [Competitor Flag] you can easily add it to the Excel file and no-needs to modify the script!

Warning: I still strongly suggest to fix this data at its source, instead of patching it with the script

Hope this help,