topic Re: date picker in QlikView

date picker

jyothish8807 — Mon, 14 Apr 2014 06:09:29 GMT

Hi All

I am trying to auto populate the dates between MINDATE and MAXDATE:

Name MINDate MAXDate

A Jan 2009 May 2009

Now i want to convert this table into this form:

Name Date

A Jan 2009

A Feb 2009

A Mar 2009

A Apr 2009

A May 2009

Thanks & Regards

Jyothish KC

Re: date picker

tresB — Mon, 14 Apr 2014 06:18:59 GMT

PFA.

Temp:
Load * Inline [
Name, MINDate, MAXDate

A, Jan 2009, May 2009
];

Load
Name,
Date(AddMonths(Date#(MINDate,'MMM YYYY'),IterNo()-1), 'MMM YYYY') as Date
Resident Temp While AddMonths(Date#(MINDate,'MMM YYYY'),IterNo()-1)<= Date#(MAXDate, 'MMM YYYY');

drop Table Temp;

Re: date picker

MK_QSL — Mon, 14 Apr 2014 06:30:21 GMT

Temp:

Load

Name,

Date(Date#(MINDate,'MMM YYYY'),'DD/MM/YYYY') as MinDate,

Date(Date#(MAXDate,'MMM YYYY'),'DD/MM/YYYY') as MaxDate

Inline

[

Name, MINDate, MAXDate

A, Jan 2009, May 2009

];

NoConcatenate

Final:

Load

Name,

Date(AddMonths(MinDate,IterNo()-1),'MMM YYYY') as Date

Resident Temp

While AddMonths(MinDate,IterNo()-1)<=MaxDate;

Drop Table Temp;

Re: date picker

its_anandrjs — Mon, 14 Apr 2014 06:51:57 GMT

Load your table like

Tab1:

LOAD * INLINE [

Name,MINDate,MAXDate

A, Jan 2009,May 2009

];

Tab2:

LOAD

Name,

Num(Date#(MINDate,'MMM YYYY')) AS MinDate,

Num(Date#(MAXDate,'MMM YYYY')) AS MaxDate

Resident Tab1;

DROP Table Tab1;

LET vMin = Peek('MinDate',0,'Tab2');

LET vMax = Peek('MaxDate',0,'Tab2');

NoConcatenate

Data:

Load

Name,

Date(AddMonths($(vMin),IterNo()-1),'MMM YYYY') as Date

Resident Tab2

While AddMonths($(vMin),IterNo()-1) <= $(vMax);

Drop Table Tab2;

Regards

Re: date picker

jyothish8807 — Mon, 14 Apr 2014 13:49:35 GMT

can i have the same in SQL?

Re: date picker

jyothish8807 — Mon, 14 Apr 2014 13:58:16 GMT

Can you please share the same in SQL?

Re: date picker

jyothish8807 — Tue, 15 Apr 2014 05:29:12 GMT

Can you please share the same in SQL also?

Re: date picker

tresB — Tue, 15 Apr 2014 05:36:00 GMT

What exactly do you want to perform in SQL?

Re: date picker

anbu1984 — Tue, 15 Apr 2014 05:48:50 GMT

In Oracle sql

SELECT SRC_TBL.MinDate,

SRC_TBL.MaxDate,

ADD_MONTHS(TRUNC(SRC_TBL.MinDate,'MM'),RNO-1) MTH_STRT_DT

FROM SRC_TBL

LEFT OUTER JOIN

(SELECT ROWNUM RNO FROM DUAL CONNECT BY LEVEL <= 240 --Replace 240 by maximum no of months between min and max date

) RW

ON 1 = 1

WHERE ADD_MONTHS(TRUNC(MinDate,'MM'),RNO-1) <= MaxDate

Re: date picker

jyothish8807 — Tue, 15 Apr 2014 07:29:03 GMT

Its not working. I am writing this in ms access.

Re: date picker

jyothish8807 — Tue, 15 Apr 2014 07:31:30 GMT

I am trying to do the same in ms access also.I want to auto generate all the dated in between MINDATE and MAXDATE.