Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
coloful_architect

add a filter at load script

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 FlagNew Competitor Flag 
AYesNo
BYesNo
CYesNo
DYesYes
EYesYes
FYesYes
GNoNo
HNoNo
IYesYes
JNoNo
5 Replies
ArnadoSandoval
Specialist II
Specialist II

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

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
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]

Thanks & Regards,
Mayil Vahanan R
coloful_architect
Author

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
Author

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

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:

Competition_Flag_01.png

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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.