Applying Flags

[andymanu]

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

[Thiago_Justen_]

Maybe this:

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

You could put any dimension instead Prod_Name here.

[pawwy1415]

HI,

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

[andymanu]

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]

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]

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]

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]

Try This

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

[andymanu]

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_ID	Prod_Name	Quantity	Country
P100	A	30	USA
P101	B	20	NZ
P103	D	50	AUS
P101	B	35	NZ
P105	E	10	NZ
P104	F	20	SL
P106	G	30	AUS
P100	A	20	SL

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_ID	Prod_Name	Quantity	Country
P101	B	20	NZ
P101	B	35	NZ

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]

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