topic Re: How to fill date in a month with data ? in QlikView

How to fill date in a month with data ?

danipudianto — Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

I have Data Like This :

Testing:

LOAD

* INLINE [

Year,Month,Tgl,Qty,Code

2011,1,1,100,A

2011,1,3,200,B

2011,1,5,300,A

2011,2,3,1,A

2011,2,4,5,B

2011,2,7,8,B

2011,2,9,10,A

2011,2,10,12,A

];

If I Select Month =1

the table shown like this

But I want the result like this :

Can We ?

I Attach example Files

Thanks All for help

Re: How to fill date in a month with data ?

Michiel_QV_Fan — Thu, 09 Feb 2017 11:39:13 GMT

Without opening your attached file.

Basically you cannot build that table because the dates aren't in the data.

You need a master calendar that generates also the missing data and then use that in your chart and deselected hide when zero and/or show all values in your dimension.

I suggest using qlikview components to generate your masster calendar: GitHub - RobWunderlich/Qlikview-Components: A library for common Qlikview Scripting tasks

Re: How to fill date in a month with data ?

buzzy996 — Thu, 09 Feb 2017 12:23:58 GMT

u can with applymap(), create a another table with field Tlg with data from 1 to 30 and use applymap.

Re: How to fill date in a month with data ?

Anil_Babu_Samineni — Thu, 09 Feb 2017 12:57:07 GMT

You are mentioned If i select 1 then what was the intend

Q) If you select 2 then you want to show till 28 Days / What? What if the year has Leap year that case you want to show 29 on your Pivot Table

Interesting and make sense tooooo

Highly recommend for marcowedel‌ jagan mrkachhiaimpstalwar1‌ mrkachhiaimp

Re: How to fill date in a month with data ?

MK_QSL — Thu, 09 Feb 2017 19:41:55 GMT

Testing:
LOAD
  *, 
  Date#(Year&Month,'YYYYM') as YearMonth,
  Year&Month&Tgl as %Key
INLINE 
[
  Year,Month,Tgl,Qty,Code
  2011,1,1,100,A
  2011,1,3,200,B
  2011,1,5,300,A
  2011,2,3,1,A
  2011,2,4,5,B
  2011,2,7,8,B
  2011,2,9,10,A
  2011,2,10,12,A
];


NoConcatenate
Temp:
Load Date, Year(Date) as Year, Month(Date) as Month, Day(Date) as Tgl;
Load 
  Date(MinDate + IterNo() - 1) as Date
While MinDate + IterNo() -1 <= MaxDate;
Load 
  MonthStart(Min(FieldValue('YearMonth',RecNo()))) as MinDate,
  MonthEnd(Max(FieldValue('YearMonth',RecNo()))) as MaxDate
AutoGenerate FieldValueCount('YearMonth');


Join (Temp)
Load Distinct Code Resident Testing;


Concatenate(Testing)
Load * Where Not Exists(%Key);
Load *, Year&Num(Month,'0')&Tgl as %Key Resident Temp;
Drop Fields %Key, YearMonth;
Drop Table Temp;

Use the same Pivot table/Straight Table.

Go to presentation tab and untick Suppress Zero Values.

Re: How to fill date in a month with data ?

danipudianto — Fri, 10 Feb 2017 01:35:55 GMT

It's not like That.

Number 1 show month Jan, 2 = Feb

Jan consist of 31 days, Feb=28 day

If I select Jan (1), all day in Jan show in view even no data

So also I select Feb (2), all day in Feb show in view even no data

It's my question mean

Thanks

Re: How to fill date in a month with data ?

danipudianto — Fri, 10 Feb 2017 01:36:50 GMT

Thanks for your response

I will try

Re: How to fill date in a month with data ?

danipudianto — Fri, 10 Feb 2017 01:39:50 GMT

Thanks Manish

I will try to implement

Re: How to fill date in a month with data ?

danipudianto — Tue, 14 Feb 2017 04:59:56 GMT

Hai Manish,

If I add field SubInv (IF1 and IF2)

and I make List Box with SubInv Field

How query in qlikview ?

Thanks