topic Re: Create a Column for a Field in QlikView

Create a Column for a Field

Sat, 09 Feb 2013 02:41:13 GMT

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.

Re: Create a Column for a Field

Sat, 09 Feb 2013 04:21:21 GMT

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';

Re: Create a Column for a Field

Mon, 11 Feb 2013 01:47:38 GMT

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

Re: Create a Column for a Field

Sokkorn — Mon, 11 Feb 2013 02:01:42 GMT

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

Re: Create a Column for a Field

Mon, 11 Feb 2013 02:24:09 GMT

Good Day Sir,

I attach my QVD for your reference

Sir Sokkorn there no output for the TimeOut.

Thank you for your response

Re: Create a Column for a Field

nagaiank — Mon, 11 Feb 2013 02:51:46 GMT

The attached sample application seems to do what you want.

Re: Create a Column for a Field

Mon, 11 Feb 2013 03:06:13 GMT

Good Day Sir,

i already did that but the timeout is null.

Kindly see the attach file of mine for your reference

Re: Create a Column for a Field

Mon, 11 Feb 2013 04:53:31 GMT

Hi,

Please find the attached file..

Hope it helps you..

//Chandra

Re: Create a Column for a Field

Mon, 11 Feb 2013 05:01:17 GMT

Good Day kindly see the picture this is the output.

Re: Create a Column for a Field

Mon, 11 Feb 2013 05:09:28 GMT

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

Re: Create a Column for a Field

Mon, 11 Feb 2013 05:27:04 GMT

Hi,

Here the data is not proper.. for some of the records Time code=F12 is not there and i could see the time code is F32 for some records..

could you elaborate what you want exactly..

Re: Create a Column for a Field

Mon, 11 Feb 2013 05:37:52 GMT

Good day sir,

because the partner of this is F11 and F12 , F21 adn F22 [] F31and F32 but i just to test it on f11 and F12.

Thank you very much for your response.

Re: Create a Column for a Field

Mon, 11 Feb 2013 05:46:23 GMT

Good Day Sir,

Kindly see my QVD for reference.

Re: Create a Column for a Field

Sokkorn — Mon, 11 Feb 2013 06:38:14 GMT

Hi,

User this script

[TimeLog]:
LOAD idcount, 
     empcode, 
     timecode, 
     timelogs, 
     Time, 
     Date
FROM [TimeLog.qvd] (qvd);
//********* Use Pivot Table ******************
[Method1]:
LOAD
    empcode        AS [M1.EMP],
    timecode    AS [M1.Type],
    Date         AS [M1.TranDate],
    Time        AS [M1.Log]
Resident [TimeLog] Where Match(timecode,'F11','F12');
//********* Use ApplyMap **********************
[TimeIn]:
MAPPING LOAD
    empcode &'-'& Date,
    Time 
Resident [TimeLog] Where timecode='F11';
[TimeOut]:
MAPPING LOAD
    empcode &'-'& Date,
    Time 
Resident [TimeLog] Where timecode='F12';
[Method2]:
LOAD Distinct
    empcode        AS [M2.EMP],
    Date         AS [M2.TranDate],
    ApplyMap('TimeIn',empcode &'-'& Date,NULL())    AS [M2.TimeIn],
    ApplyMap('TimeOut',empcode &'-'& Date,NULL())    AS [M2.TimeOut]
Resident [TimeLog] Where Match(timecode,'F11','F12');

See sample attached file.

Regards,

Sokkorn

Re: Create a Column for a Field

phaneendra_kunc — Mon, 11 Feb 2013 07:03:32 GMT

I guess you are only intresed in for a given employeecode Time in and Time out...

See attached...

Phani

Re: Create a Column for a Field

Anonymous — Mon, 11 Feb 2013 07:41:47 GMT

Hi ,

check out this

step 1:

Directory;

LOAD empcode,

timecode,

IF(timecode='F11','ti','to') as flag,

timelogs

FROM

test123.xlsx

(ooxml, embedded labels, table is Sheet2);

step 2 :

create a chart staright table

add empcode as dimension

write two set analysis

=sum({<flag={'to'}>}timelogs) for time out

sum({<flag={'ti'}>}timelogs) for time in

you will get what you want

Re: Create a Column for a Field

Mon, 11 Feb 2013 08:22:51 GMT

Yup but a Date too.

Re: Create a Column for a Field

Mon, 11 Feb 2013 14:14:46 GMT

Perhaps I misunderstand, but how do you associate a particular time-OUT with another time-IN for any given employee? In your data, sometimes you have time-OUT's with no apparent associated time-IN, and vice-versa. Maybe I am missing something, but is there some way you tie the time-OUT records to a specific time-IN record?

I put together an example where the date of the time-IN is used to associate with a time-OUT. I've attached QVW, and code is below.


TimeLog:
Load
   *,
   empcode & Date(Date,'YYYYMMDD') As empkey;
LOAD 
   idcount, 
   empcode, 
   timecode, 
   timelogs, 
   Time, 
   Date
FROM
TimeLog.qvd (qvd);
 
LogData:
LOAD 
   empcode,
   empcode & Date(Date,'YYYYMMDD') As empkey, 
   timelogs As TimeIn
Resident TimeLog WHERE timecode='F11';
 
Left Join Load
   empcode & Date(Date,'YYYYMMDD') As empkey, 
   timelogs As TimeOut
Resident TimeLog WHERE timecode='F12';
 
Drop Table TimeLog;

This assumes the time-OUT will always occur on the same day as the time-IN. If that is not so, then you'll either need another field to help associate the two events, or you could construct a model that assumes the time-IN's and time-OUT's are sequential (i.e., OUT always follows IN). But again, I see problems in the data with this. You sometimes have multiple OUT's with no apparent corresponding IN record, etc.

Re: Create a Column for a Field

Thu, 04 Dec 2014 15:17:56 GMT

Hi,

Source :

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

Script

Table:

LOAD EmpCode,

//TimeCode,

TimeLogs as Timein

FROM

(ooxml, embedded labels, table is Tab3)

Where TimeCode='F11';

inner join

LOAD EmpCode,

//TimeCode,

TimeLogs as Timeout

FROM

(ooxml, embedded labels, table is Tab3)

Where TimeCode='F12';

Result :

EmpCode	Timein	Timeout
Sample1	8:00	7:00
Sample2	8:30	7:30