topic Re: Show a whole Week Data in QlikView

Show a whole Week Data

Anonymous — Wed, 16 Dec 2015 07:09:51 GMT

Hi I have a question,

i have data in pivot table like this:

DIM3 A B

DIM1 DATE

A 26 Oct 2015 10 20

27 Oct 2015 1 1

I only have two data on that week (26 Okt and 27 Okt), but i want to show a whole week, so the result will be like this:

DIM3 A B

DIM1 DATE

A 26 Oct 2015 10 20

27 Oct 2015 1 1

28 Oct 2015 - -

29 Oct 2015 - -

30 Oct 2015 - -

31 Oct 2015 - -

If there is no data on that day, it will show "-"

Is it possible to make it ?

Really appreciate your help.

Thanks

Re: Show a whole Week Data

Anonymous — Wed, 16 Dec 2015 07:13:07 GMT

if you define a mastercalendar with all Dates it should be possible

you should unclick "suppress Zero values" (presentation tab) or "show all values" for Dimension if available

Re: Show a whole Week Data

MK_QSL — Wed, 16 Dec 2015 07:13:44 GMT

If you have all Dates associated with either A or B, simply untick Suppress Zero Values from Presentation Tab.

But if you don't have and value Associated with A and B for all Dates, you need to create those records with null values.

Generating Missing Data In QlikView

Re: Show a whole Week Data

Wed, 16 Dec 2015 07:20:32 GMT

Using a master calendar will help.

Re: Show a whole Week Data

Anonymous — Wed, 16 Dec 2015 07:21:39 GMT

Hi thanks for your suggestion,

I have try to left join my master calendar with my data.

The structure of my data will be like this

DATE DIM1 QTY

26 Oct 2015 A 20

27 Oct 2015 A 1

28 Oct 2015 - -

29 Oct 2015 - -

30 Oct 2015 - -

31 Oct 2015 - -

But the problem is i need to show DIM1 on my pivot table. and it must be not null

any idea ?

Thanks in advance

Re: Show a whole Week Data

Wed, 16 Dec 2015 14:02:05 GMT

Can you attach a sample app to test ideas on.

Re: Show a whole Week Data

Anonymous — Thu, 17 Dec 2015 01:56:48 GMT

Hi,

Here is my data

The result I expect is like this

Desc Qty Date Week

A - 16-11-2015 47

A - 17-11-2015 47

A - 18-11-2015 47

A 1 19-11-2015 47

A - 20-11-2015 47

A 2 21-11-2015 47

A - 22-11-2015 47

B - 16-11-2015 47

B - 17-11-2015 47

B - 18-11-2015 47

B - 19-11-2015 47

B 3 20-11-2015 47

B - 21-11-2015 47

B 4 22-11-2015 47

Thanks in advance

Really appreciate your help.

Re: Show a whole Week Data

MayilVahanan — Thu, 17 Dec 2015 04:09:01 GMT

Try like this

Data:

LOAD Date(Date#(Date,'DD-MM-YYYY'),'DD/MM/YYYY') as Date,

Week

FROM

[Left Join Data.xlsx]

(ooxml, embedded labels, table is Sheet2);

left join(Data)

LOAD Desc,

Date(Date#(Date,'DD-MM-YYYY'),'DD/MM/YYYY') as Date,

Qty

FROM

[Left Join Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

MaxDateTmp:

Load Max(Date) as MaxDate, Min(Date) as MinDate Resident Data;

Let vMaxDate = Peek('MaxDate', -1, 'MaxDateTemp');

Let vMinDate = Peek('MinDate', -1, 'MaxDateTemp');

Join(Data)

LOAD Desc as Desc, Week( Date($(vMinDate) + IterNo()-1)) as Week, Date($(vMinDate) + IterNo()-1) as Date Resident Data While ($(vMinDate) + IterNo()-1) <= $(vMaxDate);

FinalData:

NoConcatenate

Load * Resident Data Where not isnull(Desc );

DROP Table Data, MaxDateTmp;

Output:

Desc	Qty	Date	Week
A		16/11/2015	47
A		17/11/2015	47
A		18/11/2015	47
A	1	19/11/2015	47
A		20/11/2015	47
A	2	21/11/2015	47
A		22/11/2015	47
B		16/11/2015	47
B		17/11/2015	47
B		18/11/2015	47
B		19/11/2015	47
B	3	20/11/2015	47
B		21/11/2015	47
B	4	22/11/2015	47

Re: Show a whole Week Data

tyagishaila — Thu, 17 Dec 2015 05:23:41 GMT

you can autogenerate whole week dates or complete month dates..

Load Desc,

Date,

Qty

from .....................qvd;

outer join

NewDate:

Load

Date(today()-RecNo(), 'DD-MM-YYYY') as Date

Autogenerate 7 ;

Note: 7 used to generate 7 dates, if you want 1 month dates use 30 in place of 7.

Re: Show a whole Week Data

Anonymous — Thu, 17 Dec 2015 07:17:09 GMT

Hi,

Please see the below post:

The Master Calendar

Re: Show a whole Week Data

Kushal_Chawda — Thu, 17 Dec 2015 07:36:57 GMT

like this

Data:

LOAD date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY') as Date,

Week

FROM

(ooxml, embedded labels, table is Sheet2);

Join

LOAD Desc,

date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY') as Date,

Qty

FROM

(ooxml, embedded labels, table is Sheet1);

MinMax:

LOAD max(Date) as MaxDate,

min(Date) as MinDate

Resident Data;

let vMaxDate= Peek('MaxDate',0,'MinMax');

let vMinDate= Peek('MinDate',0,'MinMax');

New:

LOAD Distinct Desc,

Week,

date($(vMinDate)+IterNo()-1,'DD-MM-YYYY') as Date

Resident Data

While $(vMinDate)+IterNo()-1<=$(vMaxDate) and not IsNull(Desc);

Left Join

LOAD Distinct

Date,

Desc,

Qty

Resident Data;

DROP Table Data,MinMax;