topic Re: Date in QlikView

Date

smilingjohn — Fri, 16 Oct 2015 04:20:52 GMT

Hi All

after succefully runing the Master calendar , when i tried to check the date this is what i find , I dont match with the existing date ,and also when i see in table view the subset ratio is only

PLz find the attachment .

Re: Date

mightyqlikers — Fri, 16 Oct 2015 04:31:32 GMT

hi,

try date format like

date(date#(datefield,'DD-MM-YYYY HH:mm:SS'),'DD-MM-YYYY')

Regards
$@M

Re: Date

jsingh71 — Fri, 16 Oct 2015 04:34:14 GMT

Try

Date(Date#(<Your Date Field>,'Your Date Field Format'), <'Desired Date Format'>)

Ex: Date(Date#(DateField, 'YYYY/MM/DD'), 'MM/DD/YYYY')

If possible share sample application.

Re: Date

tamilarasu — Fri, 16 Oct 2015 04:34:35 GMT

Hi John,

Subset Ratio shows you the percentage of values exists in the current table by comparing with the overall distinct values of that field.

Say for example Master calendar table has 100 date values (Date column) and Product table having only 50 date values (Date column) then the Subset Ratio for master calendar is 50% (50/100) and Subset Ratio for Product is 100% (100/100).

I could see your date field formatted as DD/MM/YYYY hh:mm:ss (Differ from master calendar). Use proper date function that matches with your master calendar.

Hope this helps you.

Re: Date

smilingjohn — Fri, 16 Oct 2015 04:55:20 GMT

HI Nagraj U r back , thanks for the explanation .

Please find my QVW file , u can see that in the tabular format the date is not matching , If u can please suggest me it will be great .

Re: Date

jonathandienst — Fri, 16 Oct 2015 05:03:32 GMT

Create a date field without the time component, or split it into a date part and a time part during load, like this:

LOAD ....

Date(Floor(Date)) As Date,

Time(Frac(Date)) As Time,

This assumes that the field Date is correctly read as a numeric date value.

Re: Date

smilingjohn — Fri, 16 Oct 2015 05:09:56 GMT

Hi Jonathan , If u please look at my QVW file above it will be really helpfull for me ,

I tried ur option but yet iam getting the same thing , when i add all the fields in a table , the master calendar fields dont match they just keep below

Re: Date

tamilarasu — Fri, 16 Oct 2015 05:09:56 GMT

Yes. Because I am looking the same excel file of yours from yesterday.

You can see your transaction date format is "DD-MM-YYYY HH:mm:SS TT" (Not DD-MM-YYYY HH:mm:SS ) you missed the AM/PM format. Change it like below

date(date#(TRANSACTION_DATE,'DD-MM-YYYY HH:mm:SS TT'),'DD-MM-YYYY')as Date  ,

Also I could see wrong date format for the column Period name. Change like below

date(date#(PERIOD_NAME,'MM-DD-YYYY'),'MM-YYYY TT')as Pmonth  ,

I guess you wrongly understood the date format by just seeing the cell. Check the formula bar everytime. You can see original date format.

Re: Date

smilingjohn — Fri, 16 Oct 2015 05:13:59 GMT

Hi Nagraj

I followed the procedure u ask me to do , after reloading it , still when u add SBU , date, other fields from master calendar they dont match

Re: Date

jonathandienst — Fri, 16 Oct 2015 05:21:17 GMT

Your field TRANSACTION_DATE is already numeric, so instead of

date(date#(TRANSACTION_DATE,'DD-MM-YYYY HH:mm:SS'),'DD-MM-YYYY')as Date ,

use this:

Date(Floor(TRANSACTION_DATE))

Re: Date

tamilarasu — Fri, 16 Oct 2015 05:24:32 GMT

Hi John,

Here the key field is Date column. Your calendar table contains 2 years date values and you can get only matching date values in ledger table. Please attach the reloaded application again.

Re: Date

smilingjohn — Fri, 16 Oct 2015 05:26:44 GMT

HI Nagaraj Thanks I understood , the date is conflicting .

You are always there Tamil Nagraj u just rockkkkkk.

Re: Date

tamilarasu — Fri, 16 Oct 2015 05:31:08 GMT

Happy to help you.