topic Re: Date Format in QlikView

Date Format

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

Hi,

I have one QVD in that i have one Month Field.

so when i try to filter in where condition by giving as below it's not returning me any Rows

Load * from XXX.qvd

where Month >='201801'

is it some problem with month field format ? can some help me.

Best Regards

Re: Date Format

MK9885 — Fri, 06 Jul 2018 16:00:16 GMT

Is that a month field or Year Month field?

Where condition seem to be correct

Re: Date Format

marcelviegas — Fri, 06 Jul 2018 16:06:08 GMT

TRY,

Load * from XXX.qvd

where date(Month,'YYYYMM') >=date#('201801','YYYYMM')

Re: Date Format

dunnalahk — Fri, 06 Jul 2018 16:07:29 GMT

Hi,

Format is below

Re: Date Format

sunny_talwar — Fri, 06 Jul 2018 16:09:26 GMT

You can also try this

Load * from XXX.qvd

where Month >=MakeDate(2018, 1)

Re: Date Format

dunnalahk — Fri, 06 Jul 2018 16:33:17 GMT

Hi,

It is working but when i try to join with my Data period BMT QVD based on month it is not working

Load * from XXX.qvd

where date(Month,'YYYYMM') >=date#('201801','YYYYMM')

Inner JOIN

LOAD Year,

Quarter,

Month,

FROM

[$(QVD_Path)\DataPeriod.qvd]

(qvd)

Problem is Format not coming as Expected from source QVD, that's the reason when i try to join with my BMT its not working as per below snapshot

you can see 43221 is coming for 201805 month

and 43252 is coming for 201806 month

;

Re: Date Format

marcelviegas — Fri, 06 Jul 2018 16:47:21 GMT

try,

Load * from XXX.qvd

where date(Month,'YYYYMM') >=date#('201801','YYYYMM')

Inner JOIN

LOAD Year&Month as month,Quarter

FROM

[$(QVD_Path)\DataPeriod.qvd]

(qvd)

Re: Date Format

marcelviegas — Fri, 06 Jul 2018 16:48:48 GMT

it's easier if you attach qvw

Re: Date Format

dunnalahk — Fri, 06 Jul 2018 16:49:36 GMT

Hi Sunny,

it works as i said in my earlier reply.

but the problem i if i join with my data period BMT QVD month to month it's not working as expected.

could you help me please.

Best Regards,

Re: Date Format

sunny_talwar — Fri, 06 Jul 2018 16:53:20 GMT

So, is your problem just the format or you think that the join isn't correct?

Re: Date Format

dunnalahk — Fri, 06 Jul 2018 17:01:03 GMT

Hi,

I have attached to Original post.

Best Regards,

Re: Date Format

dunnalahk — Fri, 06 Jul 2018 17:01:45 GMT

HI Sunny,

I have attached the QVW to original post.

I am thinking its format issue.

Best Regards,

Re: Date Format

elopez7811 — Fri, 06 Jul 2018 17:57:53 GMT

hello, I made a separation of bases forming qvd in 3 phases before reaching the final model.

1. consolidated; it accumulates my base as it normally arrives
2. transformation; the most important where our facility for dates is scheduled

date(Floor(CREATIONDATE),'DD/MM/YYYY') as CREATIONDATE,

Day(CREATIONDATE) as CREATIONDAY,

Month(CREATIONDATE) as CREATIONMONTH,

Year(CREATIONDATE) as CREATIONYEAR,

(WeekDay(date(CREATIONDATE,'DD/MM/YYYY'))&' '&Day(CREATIONDATE) )as CREATIONDAYNAME,

Time(CREATIONDATE) as CREATIONTIME,

Hour(CREATIONDATE) as CREATIONHOUR,

a single date separating the whole body

3.esquema: donde se reune todo en un qvd final

and now in the final model, performing the analysis set facilitates many things

CREATIONYEAR = {'$(=Max(CREATIONYEAR))','$(=Max(CREATIONYEAR)-1)','$(=Max(CREATIONYEAR)-2)'}

for example

Re: Date Format

nishanthi_8 — Mon, 09 Jul 2018 05:18:23 GMT

What is the key field used to join both the tables ? Are you using "Month" field from first qvd ?

Re: Date Format

qlikviewwizard — Mon, 09 Jul 2018 08:19:16 GMT

What is the format of RMRMonth? Is it YYYYMM?

If yes, please use this:

DATE(DATE#(RMRMonth,'YYYYMM'),'YYYYMM') >=DATE(MakeDate(2018, 5),'YYYYMM')

Refrence:

Why don’t my dates work?