8 Replies Latest reply: Oct 16, 2018 3:37 PM by Marco Wedel RSS

    Change variable based on data in load script

    Casper van Pomeren

      Hi everyone,


      First some background information:

       

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

      CountryOneCountryTwoOtherRandomData
      The NetherlandsNew Zealand1
      AustraliaUganda2
      United StatesBelgium3
      RussiaPeru4


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

      CountryOneCountryOneEUCountryTwoCountryTwoEUOtherRandomData
      The NetherlandsThe Netherlands (EU)New ZealandNew Zealand1
      AustraliaAustraliaUgandaUganda2
      United StatesUnited StatesBelgiumBelgium (EU)3
      RussiaRussiaPeruPeru4

       

      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:

       

      CountryOneCountryOneEU
      AustriaEuropean Union
      BelgiumEuropean Union
      BulgariaEuropean Union
      CroatiaEuropean Union
      CyprusEuropean Union
      Czech RepublicEuropean Union
      etcEuropean 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:

      CountryOneCountryOneEU
      The NetherlandsThe Netherlands (EU)
      AustraliaAustralia
      United StatesUnited States
      RussiaRussia
      AustriaEuropean Union
      BelgiumEuropean Union
      BulgariaEuropean Union
      CroatiaEuropean Union
      CyprusEuropean Union
      Czech RepublicEuropean Union
      etcEuropean 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.

        • Re: Change variable based on data in load script
          Dilip Ranjith

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

          • Re: Change variable based on data in load script
            Marco Wedel

            Hi,

             

            maybe one solution might be:

             

            QlikCommunity_Thread_316488_Pic1.JPG

            QlikCommunity_Thread_316488_Pic2.JPG

             

            QlikCommunity_Thread_316488_Pic3.JPG

            QlikCommunity_Thread_316488_Pic4.JPG

             

            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

              • Re: Change variable based on data in load script
                Casper van Pomeren

                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 name1
                Belgium1
                France1
                Germany1
                Italy1
                Luxembourg1
                Netherlands1
                Denmark1
                Ireland1
                United Kingdom1
                Greece1
                Portugal1
                Spain1
                Austria1
                Finland1
                Sweden1
                Cyprus1
                Czech Republic1
                Estonia1
                Hungary1
                Latvia1
                Lithuania1
                Malta1
                Poland1
                Slovakia1
                Slovenia1
                Bulgaria1
                Romania1
                Croatia1


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

                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:

                CountryOneIsEUmember
                Netherlands1
                Australia
                United States
                Russia
                France1
                Australia
                Spain1
                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/CounterFunctions/IterNo.htm 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.