Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
];
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;
The range isn't associating with the 2 years, it did for only one. it should be
2008 - 2009 ==> 2008,2009
Same issue here the Date example 2013 - 2012 should relate to 2012 and 2013 not only 2012
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;
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