topic Re: Date in Visualization and Usability

Date

qlikview979 — Thu, 05 May 2016 18:26:34 GMT

Hi Experts,

I have one Excel file Sheet1 having one column date like JAN1 to JAN30 and sheet having "Startdate" and "Enddate" fields.

if i give in sheet 2 like

Startdate Enddate

JAN5 JAN20

i want here date from JAN5 to JAN20

Expected output:-

jan5

jan20

Regards,

Mahesh.

Re: Date

qlikview979 — Fri, 06 May 2016 05:12:59 GMT

Hi,

Can any one give solution to my requirement.

Re: Date

settu_periasamy — Fri, 06 May 2016 05:22:03 GMT

Can you share you excel and expected output?

may be like this?

T1:
LOAD * INLINE [
StartDate, EndDate
1/1/2016, 1/31/2016
2/1/2016, 2/10/2016
];

NoConcatenate
T2:
LOAD Date(StartDate+IterNo()-1) as Date
Resident T1 While Date(StartDate+IterNo()-1)<=EndDate;

DROP Table T1;

Re: Date

qlikview979 — Fri, 06 May 2016 05:35:34 GMT

Hi,

I am using this script but my where condition is not working.

T1:

LOAD StartDate,

EndDate

FROM

(ooxml, embedded labels, table is Sheet2);

let vpeek=Peek('StartDate',0,'T1');

let vpeek1=Peek('EndDate',0,'T1');

DROP table T1;

T2:

LOAD Date

FROM

(ooxml, embedded labels, table is Sheet1)

Where $(vpeek)>=Date and $(vpeek1)>=Date;

Re: Date

settu_periasamy — Fri, 06 May 2016 05:39:28 GMT

Did you check the vpeek and vpeek1 format? is that match your Date field?

Can you post the sample Excel file?

Re: Date

qlikview979 — Fri, 06 May 2016 05:43:00 GMT

Hi,

Please find my Attachment.

Re: Date

settu_periasamy — Fri, 06 May 2016 05:47:31 GMT

Try this..

T1:

LOAD StartDate,

EndDate

FROM

[Date Range Excel.xlsx]

(ooxml, embedded labels, table is Sheet2);

let vpeek=Peek('StartDate',0,'T1');

let vpeek1=Peek('EndDate',0,'T1');

DROP table T1;

T2:

LOAD Date

FROM

[Date Range Excel.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Date>='$(vpeek)' and Date<='$(vpeek1)';

Re: Date

qlikview979 — Fri, 06 May 2016 05:52:05 GMT

Hi,

Thanks,Its Perfectly Working.

Regards,
mahesh

Re: Date

settu_periasamy — Fri, 06 May 2016 05:54:35 GMT

Welcome.

Can you close this thread, if you got the answer?

Re: Date

jonathandienst — Fri, 06 May 2016 05:57:50 GMT

Please close your threads once they have been answered. You have dozens of open questions and its good practice to close those that have been correctly answered.

Re: Date

qlikview979 — Fri, 06 May 2016 06:08:54 GMT

Hi Jonathan,

I have closed All Threads.If i Forgot Show Me.

Regards,

Mahesh.