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: 
sarfaraz_sheikh
Contributor III
Contributor III

How to create static dimension in pivot table

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


1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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;

View solution in original post

15 Replies
Not applicable

Try like this:

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

show this as calculated dimension

Thanks

khushboo

sarfaraz_sheikh
Contributor III
Contributor III
Author

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

sarfaraz_sheikh
Contributor III
Contributor III
Author

Dear

gwassenaar

jagan

hic

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

Sarfaraz

jagan
Luminary Alumni
Luminary Alumni

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.

sarfaraz_sheikh
Contributor III
Contributor III
Author

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

sarfaraz_sheikh
Contributor III
Contributor III
Author

Dear jagan,

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

pls reply

Sarfaraz

jagan
Luminary Alumni
Luminary Alumni

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.

jagan
Luminary Alumni
Luminary Alumni

Please find attached file for solution.

Regards,

Jagan.

sarfaraz_sheikh
Contributor III
Contributor III
Author

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.

pic_LMS1.png

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