topic Re: How to create static dimension in pivot table in QlikView

How to create static dimension in pivot table

sarfaraz_sheikh — Tue, 19 May 2015 10:19:10 GMT

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

Re: How to create static dimension in pivot table

Tue, 19 May 2015 10:31:40 GMT

Try like this:

=if(isnull(Insurer_reject,'0',Insurer_reject)

show this as calculated dimension

Thanks

khushboo

Re: How to create static dimension in pivot table

sarfaraz_sheikh — Tue, 19 May 2015 10:38:02 GMT

Dear Khushboo,

It is not guranteed that which field not contain values in future .........0 values can be there or cannot be there ...like Insurer_reject the others dimension content's are also available which may contain 0 values in future.

Here the Insurer_reject is not the concern ......any idea how i can add 0 values at script level ..

Sarfaraz

Re: How to create static dimension in pivot table

sarfaraz_sheikh — Tue, 19 May 2015 11:01:22 GMT

Dear

gwassenaar‌

jagan‌

hic‌

.......any suggestion stuck in this problem since from many days..

Sarfaraz

Re: How to create static dimension in pivot table

jagan — Tue, 19 May 2015 11:02:07 GMT

Hi,

You need to insert the missing records for the Ergo loc, example insurer_reject is missing for Chennai, so you need to identify and load the records with 0 values.

Please find below the sample script

Data:

LOAD

Location & '_' & Type AS Key;

LOAD

If(Len(Trim(Location)) = 0, Null(), Location) AS Location,

Type,

Qty

INLINE [

Location, Type, Qty

Chennai, TotalLeads_Considered, 100

Chennai, Follow_Up, 200

,Test, 200];

// In the above script Test (Type) is missing for Chennai, TotalLeads_Considered & Follow_Up is missing for Empy Location so we need to add dummy records for this. To add dummy records use below script

Temp:

LOAD

DISTINCT Location

RESIDENT Data;

LEFT JOIN (Temp)

LOAD

DISTINCT Type

RESIDENT Data;

Concatenate(Data)

LOAD

Location & '_' & Type AS Key,

0 AS Qty

RESIDENT Temp

WHERE Not Exists(Key, Location & '_' & Type);

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

Re: How to create static dimension in pivot table

sarfaraz_sheikh — Tue, 19 May 2015 11:12:02 GMT

Dear Jagan,

little bit not understanding what you wrote in above ...request you to do changes in my attached.qvw file so that would be more helpful .......

sarfaraz

Re: How to create static dimension in pivot table

sarfaraz_sheikh — Tue, 19 May 2015 12:16:18 GMT

Dear jagan,

Kindly write solution in attached QVW .....not understood what you wrote above .....example would be more helful ...

pls reply

Sarfaraz

Re: How to create static dimension in pivot table

jagan — Wed, 20 May 2015 03:29:41 GMT

Hi,

Try this script

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 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]

FROM

[LMS_Testing_Discussion.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD

DISTINCT Ergo_loc

RESIDENT LMS_Testing;

LEFT JOIN (Temp)

LOAD

DISTINCT STATUS_ID

RESIDENT Status;

Concatenate(LMS_Testing)

LOAD

Ergo_loc & '_' & STATUS_ID 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);

DROP TABLE Temp;

Regards,

Jagan.

Re: How to create static dimension in pivot table

jagan — Wed, 20 May 2015 03:31:17 GMT

Please find attached file for solution.

Regards,

Jagan.

Re: How to create static dimension in pivot table

sarfaraz_sheikh — Wed, 20 May 2015 07:04:43 GMT

Dear Jagan,

The same solution i have implemented in my project app. But did not see as expected output in project app.Look at below snapshot for more clarity.

my project app script attched here for your reference....whenever i am selecting date over "LEAD_GENERATED_DATE " field ......the 0 values column is not coming. Is i am doing wrong somewhere ??....kindly suggest me !!!

Sarfaraz

Re: How to create static dimension in pivot table

jagan — Wed, 20 May 2015 08:46:18 GMT

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.

Re: How to create static dimension in pivot table

sarfaraz_sheikh — Wed, 20 May 2015 08:56:48 GMT

Dear Jagan,

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

Sarfaraz

Re: How to create static dimension in pivot table

jagan — Wed, 20 May 2015 09:28:06 GMT

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;

Re: How to create static dimension in pivot table

jagan — Wed, 20 May 2015 09:28:37 GMT

Hi,

Please find attached file with the changes.

Regards,

Jagan.

Re: How to create static dimension in pivot table

punitpopli — Wed, 20 May 2015 11:02:36 GMT

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

Re: How to create static dimension in pivot table

sarfaraz_sheikh — Mon, 25 May 2015 10:11:29 GMT

Dear Jagan,

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

Sarfaraz