9 Replies Latest reply: Jun 19, 2018 8:55 PM by Andy Manuja RSS

    Applying Flags

    Andy Manuja

      Hi,

      I got a sample table listed below which got some dummy sales data.

       

      Assume I want to filter the data where the the "Country" should be 'NZ'.

      Please refer my code as well;

       

      Temp:

      LOAD

          Prod_ID,

          Prod_Name,

          Quantity,

          Country,

          If(Country = 'NZ', 1 , 0) as Flag

      FROM [lib://(XXXX)/Data\Sales_Data\ProductsB.xlsx]

      (ooxml, embedded labels, table is Products);

       

       

      Sample Data table,

       

      The above table depicts the entire set of data.

      However, could anyone help me with the steps of applying the Flag something like "If (Flag = 1)," so that the above table will only show the data relevant to  country "NZ"?

       

      Thank you in advance.

       

      Regards,

       

      Andy

        • Re: Applying Flags
          Thiago Justen Teixeira

          Maybe this:

           

          Only({<Flag={1}>}Prod_Name)

           

          You could put any dimension instead Prod_Name here.

          • Re: Applying Flags
            P Kumar

            HI,

             

            Go to Data Handling. --> Uncheck Include Zero values then you can see only Flag=1 rows

              • Re: Applying Flags
                Andy Manuja

                Hi,

                Thanks for the feedback.

                 

                However, the one I am looking at found under Data Handling where you can apply a "Calculation Condition" and applying the condition.

                 

                Could you please tell me the syntax to write the expression to obtain only the data rows where the Flag value is "!"?

                 

                Note

              • Re: Applying Flags
                Andy Manuja

                Hi,

                Could someone kindly provide the steps to write the correct syntax to retrieve the data which fulfills the Flag value of "1".?

                 

                I want to view/filter the data which only fulfills the Flag value of "1".

                 

                Note,

                I could use WHERE clause to retrieve only the data where the country is ="NZ".  However, this drops the data where the Flag value not equals to "!".

                Ex- 1 as Flag

                Resident (Table Name)

                Where Country = 'NZ'

                 

                However, want to accomplish the task using the IF condition.and filter the table data based on the Flag value (which is "1").

                 

                Appreciate your feedback soon as possible.

                 

                Thanks in advance.

                 

                Regards,

                Andy

                • Re: Applying Flags
                  kaan erisen

                  Hi Andy,

                   

                  Actually you don't need to add a flag field to accomplish your goal. you can easily do that with a quantity expression with sum({<Country={'NZ'}>}Quantity).

                   

                  But If you need this flag field for other purposes, you can change the quantity expression as sum({<Flag={1}>}Quantity), table shows only flag value 1 rows.

                   

                  If you add quantity as dimension to the table, delete this field from the table and add it as a measure with the expression above. it will solve your problem.

                   

                  Hope it helps.

                  • Re: Applying Flags
                    gowtham Ganapathi

                    Try This

                     

                    sum({<Flag={1},Country={'NZ'}>}Quantity)

                    • Re: Applying Flags
                      Andy Manuja

                      Hi,

                       

                      Still I am bit unclear whow to perform it. Actually, my real data set is a large and bit complex than what i have posted (simply because of the clarity). In the said data set, I have got number of Flags for different requirements and finally want to retrieve the data where the Flag values are "1".

                       

                      Let me explain my requirement. using the same data set i have used above,

                      Consider the below table,

                       

                      Prod_IDProd_NameQuantityCountry
                      P100A30USA
                      P101B20NZ
                      P103D50AUS
                      P101B35NZ
                      P105E10NZ
                      P104F20SL
                      P106G30AUS
                      P100A20SL

                       

                      Assume, I want to retrieve the data where the country is equal to "NZ" (Flag1) and the Quantity is greater than 15 (Flag2).

                      Thus, once I load the data in to Qlik Sense it will load all 8 records to the Temp Table I have created. However, if i want to visualise the data (using the Table property in qlik sense) I need to see only the filtered data (Country = NZ and Quantity >15). Effectively the data I want to use for the visualization should be something like below,

                       

                      ( Resultant Table)

                       

                      Prod_IDProd_NameQuantityCountry
                      P101B20NZ
                      P101B35NZ

                       

                      Using the data load editor, I can load all the data to Qlik Sense.

                       

                      Temp:

                      LOAD

                          Prod_ID,

                          Prod_Name,

                          Quantity,

                          Country,

                          If(Country = 'NZ', 1 , 0) as Flag1,

                          If(Quantity>15,1,0) as Flag2

                      FROM (Data Source connection);

                       

                      Question,

                      Could anyone help me with the subsequent steps with the things I need to do in order to reach at the final stage of "Resultant Table"

                      Since the flags are already created to filter the required data, I want to use the said flag to develop the final table which is, "Resultant Table".

                       

                      Note

                      Appreciate a lot, if you could provide the answer in a step by step approach for me to implement in my real data set without any issue.

                      (Something do with expressions like "If(Flag1=1..... and If(Flag2=1,......., etc)

                       

                      Thank you in advance.

                       

                      Regards,

                      Andy

                      • Re: Applying Flags
                        Andy Manuja

                        Hi,

                         

                        Could anyone please help me to resolve the below issue.

                         

                        Trying till yesterday but still couldn't fixed it. Table 1 depicts the entire data set that I have loaded to Qlik Sense app. My objective is to filter the data which only fulfills the Flag value of "1".

                         

                        I wrote the below mentioned expression on the data visualization view.

                        Expression I used is, "Count(If(Flag1=1,1,0))"

                         

                         

                        Table1:

                         

                        However, when i run the expression I am getting an error as shown below,

                         

                         

                        I am bit surprised to see the  "Invalid Dimension" error message as shown above. I could have used a single flag to accomplish both the conditions (Country = 'NZ' and Quantity >15).

                         

                        Note

                        I have used only a single filter above (only the Flag1). A similar approach will take to Flag2 upon successfully resolving the above issue.

                         

                        Appreciate if anyone could help me to resolve the above mentioned issue as soon as possible.

                         

                        Thank you in advance.

                         

                        Kind regards,

                         

                        Andy