it is possible for me to modify the QV raw data ?

[#1]

Hi All

I need use to Edit  the QV data ?

Can some one share with me how to do this in QV ?

For example 1 row of record , show Iman is late for 00:44 min , how to make 00:44 as null ?

Paul

[jagan]

Hi Paul,

Please find attached file for solution.

Regards

Jagan.

[#3]

Hi Jagan

Thank you approach does work.

Can you pls help me add the script with one more condition. so that it will not remove all late = 44 min.

[jagan]

Hi Paul,

Try like this

LOAD

*

FROM DataSource

WHERE NOT Match(Late, '00:44', 'Time2', 'Time3', ...................);

Regards,

Jagan.

[#5]

Hi Jagan

Thank you for your advise.

For the Late i want to remove when there it special reasons.

My concern is if the late happen to be 00:44  happen on both date. it is possible to add one more condition by add the date ? so that it will remove the specifc date when late is 00:44

Paul

[jagan]

Hi Paul,

Try like this

LOAD

*

FROM DataSource

WHERE NOT Match(Late, '00:44', 'Time2', 'Time3', ...................) AND Date(Date) = MakeDate(2013, 12, 4);

Regards,

Jagan.

[#7]

Hi Jagan

I try your propose script. i cannot get it work. can you share with me pls.

Paul

[jagan]

Hi Paul,

Try this script.

ABSEN:

LOAD [AC-No.],

     Name,

  if (Name='Iman Dwi Putro','iMan',

  if (Name='Muhammar Kadaffi','KD',

  if (Name='Uni Suprihani','Uni',

  if (Name='Revi A. Kusuma','Revi',

  if (Name='Damai Fajar S.','Damai',

  if (Name='Nurul Huda','Nurul',

  )))))) as [Name_], 

     Date,

     Date as date,

Date as AutoDate_new,    

     Timetable,

     if(Len([Late]) >1,'Late','No Late') as Flag_Late,   

     if(Len([Clock In]) <1,'Null','Time in') as Flag_In,

     if(Len([Clock Out]) <1,'Null','Time Out') as Flag_Out,

     [Clock In],

     [Clock Out],

     Late,

     Early,

     Absent,

     [OT Time]

FROM

[Absen.xlsx]

(ooxml, embedded labels, table is [17Nov-22Nov])

WHERE

NOT Match(Late, '00:44') AND

Date(Date#(Date, 'MM/DD/YYYY')) <> MakeDate(2013, 11, 22);

Regards,

Jagan.

[#9]

Hi Japan

Now work fine for one date and time. When I add the 2nd date and time . I will error on makedate I don't know how separate the 2 date.

Sent from Samsung Mobile

[jagan]

Hi,

Try like this

WHERE

NOT Match(Late, '00:44', 'Give Time2 here', 'Give Time3 here') AND

(Date(Date#(Date, 'MM/DD/YYYY')) <> MakeDate(2013, 11, 22) AND Date(Date#(Date, 'MM/DD/YYYY')) <> MakeDate(2013, 11, 23), Date(Date#(Date, 'MM/DD/YYYY')) <> MakeDate(2013, 11, 24))

OR try like this

WHERE (NOT Match(Late, '00:44') AND Date(Date#(Date, 'MM/DD/YYYY')) <> MakeDate(2013, 11, 22)) AND

(NOT Match(Late, '00:24') AND Date(Date#(Date, 'MM/DD/YYYY')) <> MakeDate(2013, 11, 23))

If not works can you tell what second time and date to filter.

Hope this helps you.

Regards,

Jagan.