Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys!
I have a person that has colors and signed dates. Is there somehow i could isolate the dates between the colors. Like i need the signed dates for the colors red so the the dates should be so if the color is red it should get the the date after .
Example how it should look based on the data in the end
2014-05-08 to 2014-11-05
2015-05-12 to 2016-06-22
2016-06-22 to 2016-07-15
2016-08-01 to 2016-08-09
And if thats the last signed date it should stop.
Any ideas om stuck
Person | Signed | PGValue | Color |
Alfred | 2014-05-08 | 8 | Red |
Alfred | 2014-11-05 | 5 | Yellow |
Alfred | 2015-05-12 | 5 | Red |
Alfred | 2016-06-22 | 5 | Red |
Alfred | 2016-07-15 | 8 | Grön |
Alfred | 2016-07-20 | 5 | Gul |
Alfred | 2016-08-01 | 5 | Red |
Alfred | 2016-08-09 | 5 | Red |
Fine. Try this then:
Data:
LOAD
Person,
Date(Date#(Signed,'YYYY-MM-DD'),'YYYY-MM-DD') as Signed,
PGValue,
Color
INLINE [
Person, Signed, PGValue, Color
Alfred, 2014-05-08, 8, Red
Alfred, 2014-11-05, 5, Yellow
Alfred, 2015-05-12, 5, Red
Alfred, 2016-06-22, 5, Red
Alfred, 2016-07-15, 8, Grön
Alfred, 2016-07-20, 5, Gul
Alfred, 2016-08-01, 5, Red
Alfred, 2016-08-09, 5, Red
];
Result:
LOAD *, Start & ' to ' & End as Interval WHERE Start;
LOAD
Person, Signed as End,
If(Person=Previous(Person) AND Previous(Color)='Red',Previous(Signed)) as Start
RESIDENT
Data
ORDER BY
Person, Signed
;
Did not get your requirement could you please elaborate bit more on that ..with sample data and the desired output you required
Hi
Output these dates! Based on the data below, so the date from Red color to the next color
2014-05-08 to 2014-11-05
2015-05-12 to 2016-06-22
2016-06-22 to 2016-07-15
2016-08-01 to 2016-08-09
Data
Person | Signed | PGValue | Color |
Alfred | 2014-05-08 | 8 | Red |
Alfred | 2014-11-05 | 5 | Yellow |
Alfred | 2015-05-12 | 5 | Red |
Alfred | 2016-06-22 | 5 | Red |
Alfred | 2016-07-15 | 8 | Grön |
Alfred | 2016-07-20 | 5 | Gul |
Alfred | 2016-08-01 | 5 | Red |
Alfred | 2016-08-09 | 5 | Red |
The signed date i mean from every red color to the signed date of next color
Perhaps like this:
Data:
LOAD
Person,
Date(Date#(Signed,'YYYY-MM-DD'),'YYYY-MM-DD') as Signed,
PGValue,
Color
INLINE [
Person, Signed, PGValue, Color
Alfred, 2014-05-08, 8, Red
Alfred, 2014-11-05, 5, Yellow
Alfred, 2015-05-12, 5, Red
Alfred, 2016-06-22, 5, Red
Alfred, 2016-07-15, 8, Grön
Alfred, 2016-07-20, 5, Gul
Alfred, 2016-08-01, 5, Red
Alfred, 2016-08-09, 5, Red
];
Result:
LOAD * WHERE Start;
LOAD
Person, Signed as End,
If(Person=Previous(Person) AND Previous(Color)='Red',Previous(Signed)) as Start
RESIDENT
Data
ORDER BY
Person, Signed
;
Sorry did not get the desired outcome.
Fine. Try this then:
Data:
LOAD
Person,
Date(Date#(Signed,'YYYY-MM-DD'),'YYYY-MM-DD') as Signed,
PGValue,
Color
INLINE [
Person, Signed, PGValue, Color
Alfred, 2014-05-08, 8, Red
Alfred, 2014-11-05, 5, Yellow
Alfred, 2015-05-12, 5, Red
Alfred, 2016-06-22, 5, Red
Alfred, 2016-07-15, 8, Grön
Alfred, 2016-07-20, 5, Gul
Alfred, 2016-08-01, 5, Red
Alfred, 2016-08-09, 5, Red
];
Result:
LOAD *, Start & ' to ' & End as Interval WHERE Start;
LOAD
Person, Signed as End,
If(Person=Previous(Person) AND Previous(Color)='Red',Previous(Signed)) as Start
RESIDENT
Data
ORDER BY
Person, Signed
;
Ok how would the the code look if the data was loaded from SQL?
Depends. You could replace the INLINE load with an sql statement. If you want to do all the calculations in SQL then ask your local friendly database administrator/developer
I looked for a qick fix thanks, il manage to solve it. The data i already there with all the calculations. Thank for your help