topic Re: How do i change date to range date in QlikView

How do i change date to range date

Mon, 22 Feb 2016 18:30:57 GMT

I have 2 fields

load*inline

[

Year, Number

2008, 30000

2009, 30001

2010, 30002

2011, 30003

2012, 30004

2013, 30005

2014, 30006

2015, 30007

2016, 30008

2017, 30009

2018, 30010

2019, 30011

2020, 30012

]

I wanna compare having a field like this

2018 - 2017

2017 - 2016

2016 - 2015

2015 - 2014

2014 - 2013

2013 - 2012

2012 - 2011

How do I break this Year field into this range?

Re: How do i change date to range date

sunny_talwar — Mon, 22 Feb 2016 18:35:19 GMT

I guess what is your expected output? How would number look next to these ranges?

UPDATE: Something like this?

If yes, then try this:

Table:

LOAD *,

Year & ' - ' & (Year + 1) as Range;

LOAD * Inline [

Year, Number

2008, 30000

2009, 30001

2010, 30002

2011, 30003

2012, 30004

2013, 30005

2014, 30006

2015, 30007

2016, 30008

2017, 30009

2018, 30010

2019, 30011

2020, 30012

];

Re: How do i change date to range date

maxgro — Mon, 22 Feb 2016 18:47:13 GMT

load * inline

[

Year, Number

2008, 30000

2009, 30001

2010, 30002

2011, 30003

2012, 30004

2013, 30005

2014, 30006

2015, 30007

2016, 30008

2017, 30009

2018, 30010

2019, 30011

2020, 30012

];

left join (s)

LOAD

(Year-1) & ' - ' & Year as Compare,

Year-1 as Year,

Number as NewNumber

Resident s;

Re: How do i change date to range date

Mon, 22 Feb 2016 18:58:37 GMT

The range isn't associating with the 2 years, it did for only one. it should be

2008 - 2009 ==> 2008,2009

Re: How do i change date to range date

Mon, 22 Feb 2016 19:00:29 GMT

Same issue here the Date example 2013 - 2012 should relate to 2012 and 2013 not only 2012

Re: How do i change date to range date

sunny_talwar — Mon, 22 Feb 2016 19:21:09 GMT

I think you want this:

Table:

LOAD * Inline [

Year, Number

2008, 30000

2009, 30001

2010, 30002

2011, 30003

2012, 30004

2013, 30005

2014, 30006

2015, 30007

2016, 30008

2017, 30009

2018, 30010

2019, 30011

2020, 30012

];

LinkTable:

LOAD Year,

Year & ' - ' & (Year + 1) as Range

Resident Table;

Concatenate (LinkTable)

LOAD Year + 1 as Year,

Year & ' - ' & (Year + 1) as Range

Resident Table;

Re: How do i change date to range date

MarcoWedel — Mon, 22 Feb 2016 22:22:01 GMT

Hi Ema,

one solution might be:

table1:
LOAD * INLINE [
    Year, Number
    2008, 30000
    2009, 30001
    2010, 30002
    2011, 30003
    2012, 30004
    2013, 30005
    2014, 30006
    2015, 30007
    2016, 30008
    2017, 30009
    2018, 30010
    2019, 30011
    2020, 30012
];

tabYearSelect:
LOAD Distinct
    Year,
    Dual((Year+IterNo()-1)&' - '&(Year+IterNo()-2),Year+IterNo()-2) as Year2
Resident table1
While IterNo()<3;

hope this helps

regards

Marco