Urgent help needed - Missing Data for Qlik Sense

[nich_olas]

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

[Gysbert_Wassenaar · Accepted solution]

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;

[nich_olas]

Hi,

Thanks for the answer. I want to know how can I add back my other columns into the table Result?

My actual input will consist of ID, date, time, car, status, remarks, operator, etc.

Tried to add them in the result table during loading but I need to include them in the group by, which end up my final output is wrong due to the group by. Please help.

And also I like to know how can I count the no of In and out between one and other in the expression?

Thank you.