Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a data as shown below
sys date | counts | ORG |
w2 | 12 | TCS |
w3 | 1 | TCS |
w4 | 2 | CTS |
w7 | 4 | CTS |
w8 | 5 | TCS |
w10 | 6 | CTS |
I tried to map the date field with Calendar field and I have used it as dimension
Calendar field
Calendar date |
W1 |
W2 |
W3 |
W4 |
W5 |
W6 |
W7 |
W8 |
W9 |
W10 |
basically
when I select TCS the below output has to been shown but
Calendar date | Counts | org |
W1 | 0 | TCS |
W2 | 12 | TCS |
W3 | 1 | TCS |
W4 | 0 | TCS |
W5 | 0 | TCS |
W6 | 0 | TCS |
W7 | 0 | TCS |
W8 | 5 | TCS |
W9 | 0 | TCS |
W10 | 0 | TCS |
basically Missing data has to be forced to be ZEROs, as shown above but I can see the data as below when they users are selecting the ORG as a filter
sys date | counts | ORG |
w2 | 12 | TCS |
w3 | 1 | TCS |
w8 | 5 | TCS |
Can anyone help me on this, Missing dimension to be forced to zero
but we want to see
Can anyone help me on this?
do you want something like this
then try this
Let vmin=num(MakeDate(2023,11,01));
Let vmax=num(MakeDate(2023,11,10));
Date:
load
Date($(vmin)+rowno()-1,'DD/MM/YYYY') as Date
AutoGenerate $(vmax)-$(vmin)+1;
Calendar:
load
Date as Sys_Date
resident Date;
drop table Date;
Outer Join(Calendar)
ORG:
load * Inline
[ORG
TCS
CTS
];
left join(Calendar)
load * Inline
[Sys_Date,Counts,ORG
2/11/2023,12,TCS
3/11/2023,1,TCS
4/11/2023,2,CTS
7/11/2023,4,CTS
8/11/2023,5,TCS
10/11/2023,6,CTS
];
NoConcatenate
Calendar1:
load Sys_Date,
if(isnull(Counts),0,Counts) as counts,ORG
resident Calendar;
drop table Calendar;
I’m interested in how others have done this. I too have a similar situation I’ve been thinking about. I haven’t tried it yet, but was thinking of adding a column for org to the master calendar and repeating all the dates for each org, then concatenate the org and date together to use as the master key to join the master calendar and raw data. This should ensure there is a row for every org and date combination which is where the issue lies. Add in a coalesce statement on the count column to input a 0 if the value is null and I think it will give your desired output.
thanks for your response @Jebrezov
Yup, even I have done in the above said way, doing a cross join from date related fields with ORG, but doing this the data size is becoming too big, Hence searching for an alternate solution
We appreciate sharing sample QVW or QVF to work for you.
Appreciate @Anil_Babu_Samineni But it's not Possible, and I shouldn't be doing it's Org data Please understand & thanks for your response...
if you can do this with the above mentioned data It would be Helpful! Apologies, Hope you understand
I have got till, showing all the Week Information,
basically
Final:
load Org, dim2.. resident tablename
join
load date1,date2.. resident (calendar date)
this gave me the Cross join of the table, so with all ORG's each date got associated.
Step 2:
In the main fact table,
ORG:
load Number, key , <other fields > resident Table_name
right join
load ORG, Date , key , <other date information> resident final
dimensions are coming fine, But I'm Unable to force Count(Number) which has nulls to Zero
Count(number) + 0* Count({1})
even this not Forcing it to zero...
In the above example, If I select TCS
lets say there are 15 null records while counting measure in the line chart constant 15
w1 | 15 |
w2 | 12 |
w3 | 1 |
w4 | 15 |
w5 | 15 |
w6 | 15 |
w7 | 15 |
w8 | 5 |
w9 | 15 |
w10 | 15 |
I have added this condition if(len(trim(number)) =0,0,number)
coalesce isn't working, Can anyone help me on this
If it is null, i don’t believe it will have a length.
Have you tried a set expression like the ones in the link below?
https://community.qlik.com/t5/New-to-Qlik-Sense/Count-if-not-null/td-p/11644
do you want something like this
then try this
Let vmin=num(MakeDate(2023,11,01));
Let vmax=num(MakeDate(2023,11,10));
Date:
load
Date($(vmin)+rowno()-1,'DD/MM/YYYY') as Date
AutoGenerate $(vmax)-$(vmin)+1;
Calendar:
load
Date as Sys_Date
resident Date;
drop table Date;
Outer Join(Calendar)
ORG:
load * Inline
[ORG
TCS
CTS
];
left join(Calendar)
load * Inline
[Sys_Date,Counts,ORG
2/11/2023,12,TCS
3/11/2023,1,TCS
4/11/2023,2,CTS
7/11/2023,4,CTS
8/11/2023,5,TCS
10/11/2023,6,CTS
];
NoConcatenate
Calendar1:
load Sys_Date,
if(isnull(Counts),0,Counts) as counts,ORG
resident Calendar;
drop table Calendar;
This will be helpful: Generating Missing Data In QlikView - Qlik Community - 1491394