Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andymanu
Creator II
Creator II

Applying Flags

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

9 Replies
Thiago_Justen_

Maybe this:

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

You could put any dimension instead Prod_Name here.

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
pawwy1415
Creator III
Creator III

HI,

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

andymanu
Creator II
Creator II
Author

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

andymanu
Creator II
Creator II
Author

Hi,

I wrote something like this,

If(GetSelectedCount(Flag)=1,1,0)

Got an error message stating, "The calculation condition is not fulfilled"

Appreciate if you could help me on above.

Thanks in advance

Andy

andymanu
Creator II
Creator II
Author

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

kaanerisen
Creator III
Creator III

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.

Gowtham174
Creator
Creator

Try This

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

andymanu
Creator II
Creator II
Author

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

andymanu
Creator II
Creator II
Author

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