Conditional Expression

[upaliwije]

Hi Friends

RISK_YEAR	2011			2012			2013
Item N0	Sales	Nos	Paid	Sales	Nos	Paid	Sales	Nos	Paid
22	-	-	-	12,578	1	1,400	-	-	-
23	65,777	2	138,982	-	-	-	-	-	-
24	63,554	1	22,500	63,554	2	36,100	63,554	3	28,500
25	-	-	-	-	-	-	26,805	1	6,200
26	-	-	-	62,255	1	12,500	-	-	-
41	10,596	1	4,800	-	-	-	-	-	-
42	-	-	-	-	1	37,000	-	1	29,800
43	55,108	1	8,000	51,108	4	21,833	51,108	1	80,396

In my above pivot table I have all data in display. But I want to dis only records highlighted in red. in other words  all records where all columns have figures. Pls help me to solve this problem

Thanks

[whiteline]

Hi.

There are different ways.

You can modify the data model to calculate the flag for each Item in script (one additional load with group by statement). Then use it in your expressions:

Sum({<[Item all years]={1}>} Sales)

Or you can use set analysis in your expressions to omit the unnecessary Items. 

Depending on your data model, the set expression to keep these items could be:

Sales: Sum({<[Item N0]={'=Count(distinct RISK_YEAR)=3'}>} Sales)

Or you can use the same logic as above using calculated dimension instead of Item N0. It's useful when you don't want modify each expression but can be less performance effective.

[upaliwije]

Thanks Whiteline

"You can modify the data model to calculate the flag for each Item in script (one additional load with group by statement)."

I do not understand above part can you write the sample script for me please

[whiteline]

LOAD

     [Item N0],

     Count(distinct RISK_YEAR)=3 as [Item all years]

Resident DataTable

Group by  [Item N0];

[MarcoWedel]

Table for script tests:

Item N0	RISK_YEAR	Sales	Nos	Paid
22	2011	-	-	-
23	2011	65,777	2	138,982
24	2011	63,554	1	22,500
25	2011	-	-	-
26	2011	-	-	-
41	2011	10,596	1	4,800
42	2011	-	-	-
43	2011	55,108	1	8,000
22	2012	12,578	1	1,400
23	2012	-	-	-
24	2012	63,554	2	36,100
25	2012	-	-	-
26	2012	62,255	1	12,500
41	2012	-	-	-
42	2012	-	1	37,000
43	2012	51,108	4	21,833
22	2013	-	-	-
23	2013	-	-	-
24	2013	63,554	3	28,500
25	2013	26,805	1	6,200
26	2013	-	-	-
41	2013	-	-	-
42	2013	-	1	29,800
43	2013	51,108	1	80,396

[MarcoWedel · Accepted solution]

Hi upali,

one possible solution could be:

SET minimum3 = if($1<$2 AND $1<$3, $1, if($2<$1 AND $2<$3, $2, $3));

tabData:
LOAD [Item N0],
     RISK_YEAR,
     Sales,
     Nos,
     Paid
FROM
[http://community.qlik.com/thread/112511]
(html, codepage is 1252, embedded labels, table is @2);

tabTemp:
LOAD
  [Item N0],
  $(minimum3(CountSales, CountNos, CountPaid)) as MinCount;
LOAD
  [Item N0],
  Sum(IsNum(Sales))*-1 as CountSales,
  Sum(IsNum(Nos))*-1 as CountNos,
  Sum(IsNum(Paid))*-1 as CountPaid
Resident tabData
Group By [Item N0];

Left Join (tabTemp)
LOAD
  Count(DISTINCT RISK_YEAR) as RiskYearCount
Resident tabData;

Left Join (tabData)
LOAD Distinct
  [Item N0],
  MinCount >= RiskYearCount as RecordComplete
Resident tabTemp;

DROP Table tabTemp;

This solution takes into account that not only the distinct number of RISK_YEARs per Item No decides whether the records for this Item No are complete or not. In your example there are rows with missing sales values which I also wanted to count for this check.

Hope this helps

regards

Marco