topic Show date Dates with 0 value if data not available in QlikView

Show date Dates with 0 value if data not available

raju_salmon — Mon, 08 Aug 2016 07:23:08 GMT

Hi,

I have two tables;

Fact:

Sales Amount,

Date

Calendar:

Date,

Month,

Year

I have created table daily sales, since sales amount is not there on few date, those data are not coming on my trend report.

But i need to show those dates with Zero values.

Can you please suggest anyone.

Thanks.

Re: Show date Dates with 0 value if data not available

vikasmahajan — Mon, 08 Aug 2016 08:10:04 GMT

Qlikview Can Show only available records it omits null records or where records are not there will not display in charts.

HTH

Vikas

Re: Show date Dates with 0 value if data not available

Mark_Little — Mon, 08 Aug 2016 08:27:18 GMT

Hi,

Take a look at null as value function within the script.

Mark

Re: Show date Dates with 0 value if data not available

raju_salmon — Mon, 08 Aug 2016 08:47:34 GMT

I have tried this option, but however even NULL values are not available on that particular date.

Re: Show date Dates with 0 value if data not available

raju_salmon — Mon, 08 Aug 2016 08:50:11 GMT

Can any one help is there any way to do this?

Re: Show date Dates with 0 value if data not available

Mark_Little — Mon, 08 Aug 2016 08:52:10 GMT

HI,

Yer you would need to populate them with values,

You could try a resident load after you have the calenday.

Something Like

Noconcatenate

NewData:

Load

Date

Resident Calendar

Left Join (NewData)

Load

Date,

IF(LEN(Sales)=0, 0, Sales) AS Sales

...

Resident Table;

Then drop your original.

Mark

Re: Show date Dates with 0 value if data not available

beck_bakytbek — Mon, 08 Aug 2016 09:21:55 GMT

Hi Raju,

You can use the functin (mentioned above by Mark Little) or

you can try this function:

Noconcatenate

NewData:

Load

Date

Resident Calendar

Left Join (NewData)

Load

Date,

1:if(Isnull(Sales) 0, Sales) AS Sales or

2: if len(trim(Sales)) =0, 0,Sales ) as Sales, or

3. Alt(Sales.0) as Sales

...

Resident Table;

You can select only one from 3 Options.

or you can try this way of proceeding :

Script-Area:

NullASValue *;

Set NullValue = '-';

Noconcatenate

NewData:

Load

Date

Resident Calendar

Left Join (NewData)

Load

Date,

IF(LEN(Sales)='-', 0, Sales) AS Sales

...

Resident Table;

i hope that helps

beck

Re: Show date Dates with 0 value if data not available

Frank_Hartmann — Mon, 08 Aug 2016 09:27:29 GMT

Hi Raju,

try creating a mastercalendar and connect your table to it by a common field. then use this common field as a dimension in your chart/table.

this will populate the 0-values for the missing dates.

See attached qvw.

hope this helps