topic Re: Creating a Date Range in QlikView

Creating a Date Range

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

I need to create date ranges in my dimension...

What I have:

Date

1/1/2018

1/2/2018

1/3/2018

2/1/2018

2/2/2018

2/3/2018

What I need:

Date

1/1/2018 - 1/3/2018

2/1/2018 - 2/3/2018

Can someone help with this?

Re: Creating a Date Range

vishsaggi — Mon, 20 Aug 2018 15:33:06 GMT

may be this one way of doing it?

DateConversion:

LOAD *, Month(DateField) AS DateID INLINE [

DateField

1/1/2018

1/2/2018

1/3/2018

2/1/2018

2/2/2018

2/3/2018

3/1/2018

3/2/2018

3/3/2018

];

LEFT JOIN(DateConversion)

LOAD DateID,

Date(Min(DateField),'M/D/YYYY')&'-'&Date(Max(DateField),'M/D/YYYY') AS DateRange

Resident DateConversion

Group By DateID;

Re: Creating a Date Range

kaylaramsey2 — Mon, 20 Aug 2018 15:42:54 GMT

I don' think an inline table will work. This is just an example. We have data dating back to 2017. It would end up being a massive inline table that I would have to come back and update constantly.

Re: Creating a Date Range

vishsaggi — Mon, 20 Aug 2018 15:59:58 GMT

My friend, you do not have to use Inline table, the above i just used based on your data sample. Replace your inline table with your actual table. And just create an additional column DateId in your load table then use left join. Hope you get it.