Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Try like this:
=if(isnull(Insurer_reject,'0',Insurer_reject)
show this as calculated dimension
Thanks
khushboo
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
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.
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
Dear jagan,
Kindly write solution in attached QVW .....not understood what you wrote above .....example would be more helful ...
pls reply
Sarfaraz
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.
Please find attached file for solution.
Regards,
Jagan.
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