Change variable based on data in load script

[cpomeren003]

Hi everyone,

First some background information:

I receive data that contains 2 columns of countries, something like this:

CountryOne	CountryTwo	OtherRandomData
The Netherlands	New Zealand	1
Australia	Uganda	2
United States	Belgium	3
Russia	Peru	4

I want to change this data so that I basically get this:

CountryOne	CountryOneEU	CountryTwo	CountryTwoEU	OtherRandomData
The Netherlands	The Netherlands (EU)	New Zealand	New Zealand	1
Australia	Australia	Uganda	Uganda	2
United States	United States	Belgium	Belgium (EU)	3
Russia	Russia	Peru	Peru	4

As you can see, I want the original data + a column where all the european countries have "(EU)" attached to them. The reason for this, is that I need the "CountryOne" column for my map objects and I need my CountryOneEU for my filters etc. I also want to be able to have "Euopean Union" as a country and to connect that with all the 28 EU countries.

To achieve these things, I first concatenate another file to my data that contains the 28 EU countries and the link with "European Union". Something like this:

CountryOne	CountryOneEU
Austria	European Union
Belgium	European Union
Bulgaria	European Union
Croatia	European Union
Cyprus	European Union
Czech Republic	European Union
etc	European Union

After that I go through both CountryOne and CountryTwo like this (in my load script):

   if ([CountryOne] = 'Austria' or

        [CountryOne] = 'Belgium' or

        [CountryOne] = 'Bulgaria' or

        [CountryOne]  = 'Croatia' or

        [CountryOne] = 'Cyprus' or

        [CountryOne] = 'Czech Republic',

[CountryOne] & ' (EU)', [CountryOne]) as "CountryOneEU"

This results in something like this:

CountryOne	CountryOneEU
The Netherlands	The Netherlands (EU)
Australia	Australia
United States	United States
Russia	Russia
Austria	European Union
Belgium	European Union
Bulgaria	European Union
Croatia	European Union
Cyprus	European Union
Czech Republic	European Union
etc	European Union

This works OK, but it does have some problems, for example:

- What happens if there are no EU countries in the original data from CountryOne or CountryTwo? Then we still concatenate the 28 countries / European Union table, which doesn't make any sense.

To solve this problem I tried playing around in my load script. I figured that if I only concatenate/load based on the question: does CountryOne or CountryTwo contain EU countries? I tried doing this with variables, but I can't figure out how to change a variable based on the data you are trying to load. Basically my statement I want to make is (ignore the wrong variable names, I did use let/set etc.):

var countryOneContainsEU = false;

IF CountryONE contains any of the EU countries THEN countryOneContainsEU = true;

IF countryOneContainsEU = true THEN concatenate/load EU table;

I have tried everything but I can't seem to change a variable inside my load script based on the data I am loading in. So any help there would be greatly appreciated.

I am also wondering if this is the best way to go about this, I tried rethinking my entire approach, but I haven't found a better solution yet, so if you have any ideas on how to solve my entire EU / non-eu problem please let me know.

Thanks in advance,

Casper

TLDR: I have a column with country names and I want to add " EU" to all the EU countries. I also want to add "European Union" as a country that encompasses 28 other countries. I have found something of a solution, but I only want to do this when there are EU countries present, unfortunately it currently always does my solution. I think the final solution is to change a variable based on if there are EU countries present in my data, but I can't figure out how to change a variable based on the data you are loading.

[dplr-rn]

Can you share the script of how you are trying to change the variable? is it during load statement (dont think this will work)?

[cpomeren003]

I don't have that script, since I never found a piece of script that worked. And yes, it is during the load statement. I basically tried all kinds of variations on (in the load script):

  if ([CountryOne] = 'Austria' or

        [CountryOne] = 'Belgium' or

        [CountryOne] = 'Bulgaria' or

        [CountryOne]  = 'Croatia' or

        [CountryOne] = 'Cyprus' or

        [CountryOne] = 'Czech Republic',

$(countryOneContainsEU) = true, $(countryOneContainsEU) = false,

[dplr-rn]

Ok. i dont think that works. 

Why do you need a new column?

Why not

if ([CountryOne] = 'Austria' or

        [CountryOne] = 'Belgium' or

        [CountryOne] = 'Bulgaria' or

        [CountryOne]  = 'Croatia' or

        [CountryOne] = 'Cyprus' or

        [CountryOne] = 'Czech Republic',

[CountryOne] & ' (EU)', [CountryOne]) as CountryOne

[cpomeren003]

Because:

1. I need normal country names for my map object, Belgium (EU) does not work, but Belgium does.
2. I need to be able to select "European Union" in a filter and make sure that selects all the 28 EU countries.

Thanks for thinking with me, appreciate it.

[dplr-rn]

ah ok. good luck

[MarcoWedel · Accepted solution]

Hi,

maybe one solution might be:

mapEUmembers:
Mapping
LOAD [Country name], 1
FROM [https://en.wikipedia.org/wiki/Member_state_of_the_European_Union] (html, codepage is 1252, embedded labels, table is @2);

table1:
LOAD * INLINE [
    CountryOne,    CountryTwo,  OtherRandomData
    Netherlands,   New Zealand, 1
    Australia,     Uganda,      2
    United States, Belgium,     3
    Russia,        Peru,        4
    France,        Germany,     5
    Australia,     Austria,     6
    Spain,         Mexico,      7
    Canada,        Italy,       8
];

FOR Each vCountry in 'One', 'Two'
    tabCountry$(vCountry):
    LOAD Distinct
        Country$(vCountry),
        If(IsEUmember,If(IterNo()=1,Country$(vCountry)&' (EU)','European Union'),Country$(vCountry)) as Country$(vCountry)EU
    While IterNo()<=If(IsEUmember,2,1);
    LOAD Country$(vCountry),
        ApplyMap('mapEUmembers',Country$(vCountry)) as IsEUmember
    Resident table1;
NEXT

hope this helps

regards

Marco

[cpomeren003]

Hi Marco,

Sorry for the late reply, but I haven't really had time to reply to you yet, and I didn't want to just say: Thanks, you're brilliant.

I wanted to take some time to digest your answer and hopefully add a bit to your answer (for future-me and any else that has this problem).

So before I begin: Thank you for the help, I really appreciate that you took the time to make this! Your solution works exactly how I want it to.

Now let's look at the solution step-by-step (In Qlik Sense this time, but both work).

Step 1: Mapping

Marco's code / QlikView:

mapEUmembers:

Mapping

LOAD [Country name], 1

FROM [https://en.wikipedia.org/wiki/Member_state_of_the_European_Union] (html, codepage is 1252, embedded labels, table is @2);

Qlik Sense:
1. "Create new connection"
2. "Data sources" -> "Web file"

3. "URL" -> "Countries - EUROPA  | European Union", named it: "European Countries".
4. Make selection -> Insert Script:

mapEUmembers:

Mapping

LOAD

    Countries as [Country name], 1

FROM [lib://European Countries]

(html, utf8, embedded labels, table is @2);

This basically results in a mapping table like this:

Country name	1
Belgium	1
France	1
Germany	1
Italy	1
Luxembourg	1
Netherlands	1
Denmark	1
Ireland	1
United Kingdom	1
Greece	1
Portugal	1
Spain	1
Austria	1
Finland	1
Sweden	1
Cyprus	1
Czech Republic	1
Estonia	1
Hungary	1
Latvia	1
Lithuania	1
Malta	1
Poland	1
Slovakia	1
Slovenia	1
Bulgaria	1
Romania	1
Croatia	1

To learn more about mapping, go here: https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPre...

This also helps to understand why the 1's are there and how we can use them later on.

Step 2: Loading data

Marco's code / QlikView and Qlik Sense:

table1:

LOAD * INLINE [

    CountryOne,    CountryTwo,  OtherRandomData

    Netherlands,  New Zealand, 1

    Australia,    Uganda,      2

    United States, Belgium,    3

    Russia,        Peru,        4

    France,        Germany,    5

    Australia,    Austria,    6

    Spain,        Mexico,      7

    Canada,        Italy,      8

];

This is the easiest step, just loading in some test data. In my Qlik Sense I have attached there are 4 different scenarios with each a different combination of data:
Scenario 1. CountryOne column and CountryTwo column both have no EU countries;

Scenario 2. CountryOne column includes some EU countries while the CountryTwo column does not;

Scenario 3. CountryTwo column includes some EU countries while the CountryOne column does not;

Scenario 4. CountryOne column and CountryTwo column both include some EU countries.

Step 3: For Each..next, Iterno() and While
Marco's code / Qlikview and Qlik Sense:

FOR Each vCountry in 'One', 'Two'

    tabCountry$(vCountry):

    LOAD Distinct

        Country$(vCountry),

        If(IsEUmember,If(IterNo()=1,Country$(vCountry)&' (EU)','European Union'),Country$(vCountry)) as Country$(vCountry)EU

    While IterNo()<=If(IsEUmember,2,1);

    LOAD Country$(vCountry),

        ApplyMap('mapEUmembers',Country$(vCountry)) as IsEUmember

    Resident table1;

NEXT

This step was pretty hard for me to understand, but I think I have figured it out (please correct me if I say something wrong).

So what the "For Each..next" does is basically go through line 2 to 8 once with vCountry equal to 'One' and once with vCountry equal to 'Two'. You could also change vCountry to something else, like vTest, but just make sure you change all the $(vCountry)'s to $(vTest). You could technically also change the 'One' and 'Two', but that gives all kind of problems, because Country$(vCountry) should be equal to CountryOne / CountryTwo.

So now that we know that the "For Each..next" makes sure we go through line 2 to 8 once with both 'One' and 'Two', we can focus us on the next question: What does everything inside the For Each..next do? Let's find out! To make this easier, I have translated the vCountry to 'One' and I have removed the "For Each..next" so that it's easier to read.

LOAD Distinct

    CountryOne,

    If(IsEUmember,If(IterNo()=1,CountryOne&' (EU)','European Union'),CountryOne) as CountryOneEU

While IterNo()<=If(IsEUmember,2,1);

LOAD

    CountryOne,

ApplyMap('mapEUmembers',CountryOne) as IsEUmember

Resident table1;

Ok, so let's look at the last part first, because that needs to be cleared up before we get to the If statement.

The second part basically gives us this table:

CountryOne	IsEUmember
Netherlands	1
Australia
United States
Russia
France	1
Australia
Spain	1
Canada

So every EU country has IsEUmember = 1. Then if we look at the if-statement, the condition is equal to IsEUmember. When does IsEUmember return true and when does it return false? If I understand correctly it returns true when it's equal to 1 and it returns false when it's undefined. So only the EU countries will go towards the second IF-statement. The non-EU countries don't go to the next If statement, but end with "CountryOne as CountryOneEU".

So as we have seen, the EU countries do go to the next if statement. This if statement has a IterNo(). I don't know exactly how an InterNo() works, but it reminds me of a loop that increases a var with 1 every loop. Because from what I understand from this:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/CounterFunctio...‌‌ Is that an InterNo() returns an integer indicating the current iteration within a while clause and the first iteration has number 1.

So now that we know that the first iteration has the number 1, we can solve the condition in the If statement, so IterNo()=1 equals true. This leads us to "CountryOne&' (EU)' as CountryOneEU".

We must also note that a IterNo function is only meaningful if used together with a while clause. Well we have a while statement and now that we know what the values of IterNo() and IsEUmember, we can solve it.

Solution for non-eu country:

While IterNo()<=If(IsEUmember,2,1);

While 1 <= 1 // Right side is 1, because IsEUmember is false

After we have done the first iteration of the while clause, IterNo() becomes 2, so that means:

While 2 <= 1 // While loop stops, because 2 <= is false.

This means that for a non-eu country we only go through the code once.

Solution for EU country:

While IterNo()<=If(IsEUmember,2,1);

While 1 <= 2 // Right side is 2, because IsEUmember is true

After we have done the first iteration of the while clause, IterNo() becomes 2, so that means:

While 2 <= 2 // While loop keeps going, because 2 <= 2 is true

After another iteration IterNo() becomes 3 and the while loop stops

So an EU country loops twice through the while loop. The first time, as we have seen above, it leads to "CountryOne&' (EU)' as CountryOneEU". The second time is different though, because that time IterNo() is equal to 2, and then InterNo()=1 equals false. That leads us to "'European Union' as CountryOneEU".

To summarize this part:

A non-EU country only goes once through the while loop, and has the result: "CountryOne as CountryOneEU".

An EU country goes twice through the while loop, the first loop has the result: "CountryOne&' (EU)' as CountryOneEU"

And the second time an EU country goes through the loop the result is: "'European Union' as CountryOneEU".

I think I have explained everything as best as I could and I hope I made it clearer for anyone else that has this problem in the future. If you guys have any comments / feedback I would love to hear it.

I have also attached "Example Problem.qvf" which is everything above in Qlik Sense.

Thanks again Marco!

Casper

EDIT: Well, it seems like the format is totally messed up. It seems fine to me in the editor, but the live version is without any formatting?!
EDIT2: Replace everything with quotes, hopefully this does work.

[MarcoWedel]

Hello Casper,

this is the first time I received a reply containing such an in-depth analysis of one of my example applications, I'm deeply impressed and glad I could help.

I couldn't have provided a better description of my script.

Thank you

regards

Marco