Solved: Re: How to create static dimension in pivot table - Page 2 - Qlik Community

sarfaraz_sheikh · ‎2015-05-19

Dear All,

I want to create static dimension in qlikview.....I want to show the 0 values column in my pivot table ...However whenever i checked on suppress when value is null then 0 values column will also get hide.

Can anybody help me with this...i.e. how i can acheive this requirement...how i can add 0 for null column's in pivot table.

Please have look on below scenario for more clarity of problem....

When All Status_Description Selected:

Ergo_Loc TotalLeads_Considered Follow_Up Insurer_reject Sales_closed_final

Chennai Amount 197457 11848 - 177247

count 28 2 - 25

Mumbai Amount 135475 5439 - 104281

count 20 1 - 16

- Amount 135475 5439 - 104281

count 20 1 - 16

When I Click on Suppress When Value is null on Ergo_Loc Tab then It's look like below.

Ergo_Loc TotalLeads_Considered Follow_Up Sales_closed_final

Chennai Amount 197457 11848 177247

count 28 2 25

Mumbai Amount 135475 5439 104281

count 20 1 16

My Actual Requirement is :

Ergo_Loc TotalLeads_Considered Follow_Up Insurer_reject Sales_closed_final

Chennai Amount 197457 11848 0 177247

count 28 2 0 25

Mumbai Amount 135475 5439 0 104281

count 20 1 0 16

Sarfaraz

jagan · ‎2015-05-20

Hi,

In your earlier file this columns are not there? You have to specify all the details otherwise the solution will not be correct.

For each new columns you have add like this

LEFT JOIN (Temp)

LOAD

DISTINCT NewColumn

RESIDENT TableName;

Also you have to change the key as Ergo_loc & '_' & STATUS_ID & '_' & NewColumn AS Key;

Hope this helps you.

Regards,

Jagan.

sarfaraz_sheikh · ‎2015-05-20

Dear Jagan,

Can you keep any example based on my shared script so that would be more helpful to do so ....

Sarfaraz

jagan · ‎2015-05-20

Hi,

I have included the script LEAD_GENERATED_DATE, try reloading the data

Status:

Load * inline

[STATUS_ID, Status_Description,Id

80, BInsurer_reject, 2

75, CFollow_Up, 3

77, DLead_Lost, 4

82, ELead_Pending_With_U/W, 5

79, FNon_Contactable, 6

78, GNot_Interested, 7

99, HPending_For_PPC, 8

87, ISales_Closed_Final, 9

92, ISales_Closed_Final, 9

94, ISales_Closed_Final, 9

97, ISales_Closed_Final, 9

91, ISales_Closed_Final, 9

93, ISales_Closed_Final, 9

90, ISales_Closed_Final, 9

103, ISales_Closed_Final, 9

121, ISales_Closed_Final, 9

123, ISales_Closed_Final, 9

74, KSales_Open_default, 10

114, MWork_In_Progress, 11

89, LRegenerated_Leads, 12

75, ATotal_Leads_Considered, 1

77, ATotal_Leads_Considered, 1

78, ATotal_Leads_Considered, 1

79, ATotal_Leads_Considered, 1

80, ATotal_Leads_Considered, 1

82, ATotal_Leads_Considered, 1

87, ATotal_Leads_Considered, 1

92, ATotal_Leads_Considered, 1

94, ATotal_Leads_Considered, 1

97, ATotal_Leads_Considered, 1

90, ATotal_Leads_Considered, 1

91, ATotal_Leads_Considered, 1

93, ATotal_Leads_Considered, 1

99, ATotal_Leads_Considered, 1

103, ATotal_Leads_Considered, 1

121, ATotal_Leads_Considered, 1

123, ATotal_Leads_Considered, 1

74, MTotal_Leads_Login,13

75, MTotal_Leads_Login,13

77, MTotal_Leads_Login,13

78, MTotal_Leads_Login,13

79, MTotal_Leads_Login,13

80, MTotal_Leads_Login,13

82, MTotal_Leads_Login,13

87, MTotal_Leads_Login,13

89, MTotal_Leads_Login,13

92, MTotal_Leads_Login,13

94, MTotal_Leads_Login,13

97, MTotal_Leads_Login,13

91, MTotal_Leads_Login,13

93, MTotal_Leads_Login,13

90, MTotal_Leads_Login,13

99, MTotal_Leads_Login,13

103, MTotal_Leads_Login,13

114, MTotal_Leads_Login,13

121, MTotal_Leads_Login,13

123, MTotal_Leads_Login,13

, JConversion,14

87, JConversion ,14

92, JConversion, 14

94, JConversion, 14

97, JConversion, 14

91, JConversion, 14

93, JConversion, 14

90, JConversion, 14

103, JConversion, 14

121, JConversion, 14

123, JConversion, 14

74, JConversion,14

75, JConversion,14

77, JConversion,14

78, JConversion,14

79, JConversion,14

80, JConversion,14

82, JConversion,14

114, JConversion,14

99, JConversion,14

];

LMS_Testing:

LOAD

*,

Ergo_loc & '_' & STATUS_ID & '_' & LEAD_GENERATED_DATE AS Key;

LOAD LEAD_NO,

ERGO_LOC,

if(IsNull(ERGO_LOC),'N/A',ERGO_LOC) As Ergo_loc,

PREMIUM_WITHOUT_ST,

STATUS_ID,

[Total Leads Considered],

LEAD_GENERATED_DATE

FROM

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD

DISTINCT Ergo_loc

RESIDENT LMS_Testing;

LEFT JOIN (Temp)

LOAD

DISTINCT STATUS_ID

RESIDENT Status;

LEFT JOIN (Temp)

LOAD

DISTINCT LEAD_GENERATED_DATE

RESIDENT LMS_Testing;

Concatenate(LMS_Testing)

LOAD

*,

Ergo_loc & '_' & STATUS_ID & '_' & LEAD_GENERATED_DATE AS Key,

0 AS PREMIUM_WITHOUT_ST,

0 AS [Total Leads Considered],

0 AS LEAD_NO,

Ergo_loc AS ERGO_LOC

RESIDENT Temp

WHERE Not Exists(Key, Ergo_loc & '_' & STATUS_ID & '_' & LEAD_GENERATED_DATE);

DROP TABLE Temp;

jagan · ‎2015-05-20

Hi,

Please find attached file with the changes.

Regards,

Jagan.

punitpopli · ‎2015-05-20

Hi sarfaraz.sheikh‌

I think the below link will help you to get the correct result.

Why can't I suppress 0 values in my pivot table

Have a look and check if that will helps.

Thanks,

Punit

sarfaraz_sheikh · ‎2015-05-25

Dear Jagan,

Thanks for your solution...my problem has been rectified by your solution.....Very much grateful to you ...Brilliant

Sarfaraz