topic Re: null date issue in QlikView

null date issue

yadav_anil782 — Fri, 17 Jul 2015 07:06:58 GMT

Hi qlik Experts,

when i'm doing join this table in final aap with join of transection date "RECEIPT_DATE" then i's cration null date in my qvs . what could be the resone if there is no null date and value in source table .

LOAD

DATE([Tran Date],'DD/MM/YYYY') AS DATE,

//floor(date([Tran Date])) as RECEIPT_DATE,

date([Tran Date]) as RECEIPT_DATE,

Payment,

[Project name]

FROM

(qvd);

now result is in attached

NOTE- its working fine when i'm making qvw for only this table , problem in when i'm adding in final application with all table

Re: null date issue

kamalqlik — Fri, 17 Jul 2015 07:13:18 GMT

Hi Anil,

This might be the reason because your transaction table(final_projection_excel) have values for this date only and all the other date are coming because you might be linking with the calendar which are having all Dates (which contain all transaction dates and other dates).

Do one thing just Left Keep your calendar with transaction table.

Hope this help!

Regards,

Kamal

Re: null date issue

kavita25 — Fri, 17 Jul 2015 07:46:41 GMT

Hi,

In given excel...

In Source Data, for the year 2019 the sum is 48.24

Bar Chart is showing for the year 2019 is 9.10 (Jan Month) and null values 39.14 (rest of the months) which is the total for 2019 i.e 48.24.

The issue might be while creating year field or the date format for all the Dates in your application.

In Other table sheet, the values for 2019 is 48.235... which is correct and it's giving u blank values, might be ur auto-generating the dates.

Please post a sample application if its possible for further understanding.

Hope it helps you!!

Re: null date issue

sasiparupudi1 — Fri, 17 Jul 2015 08:48:35 GMT

if you want to load only those dates that are in the qvd file, please use a where condition in your excel load

where exists(excel date([Tran Date], qvd date([Tran Date]),

Hope this helps

Re: null date issue

yadav_anil782 — Fri, 17 Jul 2015 09:29:32 GMT

Hi All,

i applyed al solutions but its not working, i'm not getting one thing when date formate is not diffrent in source field then its not showing after jan month data in 2019 , in 2019 only i can see jan month data rest coming in blank year of rest month of 2019

Re: null date issue

kavita25 — Fri, 17 Jul 2015 09:34:19 GMT

How ur linking ur [Tran Date] field with other tables??

Is the Null issue for bar chart exists while loading one table???

Re: null date issue

sasiparupudi1 — Fri, 17 Jul 2015 09:44:40 GMT

There is a mismatch in your date formats.. try the following conversion in your excel

Date(date#([Tran Date],'MM/DD/YYYY'),'DD-MMM-YYYY') as RECEIPT_DATE

Re: null date issue

sasiparupudi1 — Fri, 17 Jul 2015 09:48:27 GMT

Check which one is having MM/DD/YYY format and convert that date .. as your source table is having the date in the DD-MMM-YYYY format

HTH

Re: null date issue

yadav_anil782 — Fri, 17 Jul 2015 10:58:36 GMT

i'm linking [Tran Date] with rename of column name

script-

date([TRAN Date],'DD/MM/YYYY') as Receipt_date and all other transection dates has this same formate "dd/mm/yyyy"

Re: null date issue

sasiparupudi1 — Fri, 17 Jul 2015 11:07:32 GMT

May be then you have synthetic keys in your data model.. Are there more receipt_date fileds in other tables?..If there are try to rename them

Re: null date issue

kavita25 — Fri, 17 Jul 2015 12:00:14 GMT

Have you changed the date format on Main Tab?

or either post the script for better understanding.

Re: null date issue

avinashelite — Fri, 17 Jul 2015 12:07:28 GMT

Hi Anil,

I feel like it's a data isssue, please check the data field whether all the data are in date format are a mixture of date and text. And also cross check which join condition your using and its impact .

If you still face the issue please upload your app