Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
First, I find out the period by day using the following during loading of data.
If Time >= '0000H' and Time <='1159H', 'AM', 'PM') As Period
Second, i need to find out the status by period. For example, on 1/1/2018, the status for AM will be 'OUT' as i take the latest value. I used FirstSortedValue(Status, left(Time, -4)) in the expression. Is there anyway to do it during loading of data?
Third, I need to fill in the missing data. On 2/1/2018, the status for AM is missing (no record at all). I need to take the previous status for this. How do i do this?
Please help me with this as i need it urgent. Really appreciate it.
Below is my sample input data.
Date | Time | Car | Status |
1/1/2018 | 0800H | A | In |
1/1/2018 | 1000H | A | Out |
1/1/2018 | 1800H | A | Out |
2/1/2018 | 1730H | A | In |
3/1/2018 | 0800H | A | In |
3/1/2018 | 1700H | A | In |
Expected output:
Date | Period | Car | Status |
1/1/2018 | AM | A | Out |
1/1/2018 | PM | A | Out |
2/1/2018 | AM | A | Out |
2/1/2018 | PM | A | In |
3/1/2018 | AM | A | In |
3/1/2018 | PM | A | In |
4/1/2018 | AM | A | Out |
I also want to know is there anyway to count how many OUT are there before next IN or vice-versa.
Based on the example above, i should have
3 OUT, 3 IN, 1 OUT
Perhaps like this:
Input: LOAD *, If(Time<0.5,Dual('AM',0),Dual('PM',1)) as Period ; LOAD Date, Car, Time#(Left(Time,4),'hhmm') as Time, Status INLINE [ Date, Time, Car, Status 1/1/2018, 0800H, A, In 1/1/2018, 1000H, A, Out 1/1/2018, 1800H, A, Out 2/1/2018, 1730H, A, In 3/1/2018, 0800H, A, In 3/1/2018, 1700H, A, In ]; JOIN (Input) LOAD DISTINCT Date, Car, If(IterNo()-1,Dual('AM',0),Dual('PM',1)) as Period RESIDENT Input WHILE IterNo() <= 2 ; tmpResult: NOCONCATENATE LOAD Date, Car, Period, If(Len(Trim(Time)),Time,Time(1/4+Period/2,'hhmm')) as Time, If(Len(Trim(Status)),Status, If(Car<>Previous(Car),'Out',peek(Status))) as Status RESIDENT Input ORDER BY Car, Date, Period ; Result: NOCONCATENATE LOAD Date, Car, Period, Time(Max(Time),'hhmm') as Time, FirstSortedValue(Status, -Time) as Status RESIDENT tmpResult GROUP BY Date, Car, Period ; DROP TABLE Input, tmpResult;
Perhaps like this:
Input: LOAD *, If(Time<0.5,Dual('AM',0),Dual('PM',1)) as Period ; LOAD Date, Car, Time#(Left(Time,4),'hhmm') as Time, Status INLINE [ Date, Time, Car, Status 1/1/2018, 0800H, A, In 1/1/2018, 1000H, A, Out 1/1/2018, 1800H, A, Out 2/1/2018, 1730H, A, In 3/1/2018, 0800H, A, In 3/1/2018, 1700H, A, In ]; JOIN (Input) LOAD DISTINCT Date, Car, If(IterNo()-1,Dual('AM',0),Dual('PM',1)) as Period RESIDENT Input WHILE IterNo() <= 2 ; tmpResult: NOCONCATENATE LOAD Date, Car, Period, If(Len(Trim(Time)),Time,Time(1/4+Period/2,'hhmm')) as Time, If(Len(Trim(Status)),Status, If(Car<>Previous(Car),'Out',peek(Status))) as Status RESIDENT Input ORDER BY Car, Date, Period ; Result: NOCONCATENATE LOAD Date, Car, Period, Time(Max(Time),'hhmm') as Time, FirstSortedValue(Status, -Time) as Status RESIDENT tmpResult GROUP BY Date, Car, Period ; DROP TABLE Input, tmpResult;