Create a Column for a Field

[#1]

Good Day All

I Have a Problem, But when i Join it,

the output is not like what i want

EmpCode	TimeCode	TimeLogs
Sample1	F11	8:00
Sample2	F11	8:30
Sample1	F12	7:00
Sample2	F12	7:30

The Output must be like

EmpCode	TimeIn	TimeOut
Sample1	8:00	7:00
Sample2	8:30	7:30

TimeCode is F11=Timein and the F12=TimeOut

The output of my Application is

EmpCode	TimeIn	TimeOut
Sample1	8:00	-
Sample2	8:30	-
Sample1	-	7:00
Sample2	-	7:30

I tried to use all kind of Joins but no effect

Thank you.

[#2]

You could flatten this with a Generic Load, but easier to just load and then Join... something like this:

LogData:
LOAD EmplCode, 
     TimeLogs As TimeIn
FROM
TimeLogs.xlsx
(ooxml, embedded labels, table is Sheet1)
WHERE TimeCode='F11';
Left Join Load
          EmplCode, 
    TimeLogs As TimeOut
FROM
TimeLogs.xlsx
(ooxml, embedded labels, table is Sheet1)
WHERE TimeCode='F12';

[#3]

I've try it, All kinds of join but there's no Output

[Sokkorn]

Hi Raem,

Let try with inline and map it into your datasource

[Data]:
LOAD * INLINE [
EmpCode    ,    TimeCode    ,    TimeLogs
Sample1    ,    F11    ,    8:00
Sample2    ,    F11    ,    8:30
Sample1    ,    F12    ,    7:00
Sample2    ,    F12    ,    7:30];
[TimeLog]:
LOAD
    EmpCode,
    TimeLogs    AS [TimeIn]
Resident [Data] Where TimeCode = 'F11';
[TimeLog2]:
LEFT JOIN ([TimeLog])
LOAD
    EmpCode,
    TimeLogs    AS [TimeOut]
Resident [Data] Where TimeCode = 'F12';

Regards,

Sokkorn

[#5]

Good Day Sir,

     I attach my QVD for your reference

Sir Sokkorn there no output for the TimeOut.

Thank you for your response

[nagaiank]

The attached sample application seems to do what you want.

[#7]

Good Day Sir,

i already did that but the timeout is null.

Kindly see the attach file of mine for your reference

[#8]

Hi,

Please find the attached file..

Hope it helps you..

//Chandra

[#9]

Good Day kindly see the picture this is the output.

[#10]

hi

Data:

LOAD EmpCode,

     TimeCode,

     TimeLogs

FROM

(ooxml, embedded labels, table is Sheet1);

[TimeLog]:

LOAD

    EmpCode,

    TimeLogs    AS [TimeIn]

Resident [Data] Where TimeCode = 'F11';

[TimeLog2]:

LEFT JOIN ([TimeLog])

LOAD

    EmpCode,

    TimeLogs    AS [TimeOut]

Resident [Data] Where TimeCode = 'F12';

then output like this

EmpCode	TimeIn	TimeOut
sample1	8:00:00	7:00:00
sample2	8:30:00	7:30:00