How do i change date to range date

[#1]

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?

[sunny_talwar]

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

];

[maxgro]

s:

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;

[#4]

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

2008 - 2009  ==> 2008,2009

[#5]

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

[sunny_talwar]

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;

[MarcoWedel]

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