Reading and Analysing csv files

[#1]

Dear Mates,

This is my second post to this community and I hope to get help as I got earlier.

I have a sample of students database which have fields such as studentid,studentname,punchrecords etc.Punch record generated is in a csv mode.I need to create a straight table with studentid as dimension and punch records as expression.

punch record sample for a student on a particular date is given below

10:00:in(FD),11:15:out(FD),11:30:in(FD),14:00:out(BD),14:30:in(BD),16:30:out(BD),17:00:in(FD),18:20:out(FD),18:30:in(FD),19:00:out(FD).

INTENDED OUTPUT STRAIGHT TABLE,once we click on a studentid

Time       in/out   door 

10:00 am   in       FD   

11:15 am   out      FD   

11:30 am   in       FD   

02:00 pm   out      BD   

02:30 pm   in       BD   

04:30 pm   out      BD   

05:00 pm   in       FD

06:20 pm   out      FD   

06:30 pm   in       FD   

07:00 pm   out      FD   

From this we need to identify the time interval in minutes a particular student is in and out.

here for eg the IN period is 7 hrs and 35 minutes and out period is 1 hr 25 minutes.I tried subfield function but first row is getting empty and also error in row wise calculation.I am not able to get peek function,am using qlikview version 12.Thank you for your time.

[engishfaque]

!Dear Rishi,

Here is the data model,

LOAD

  Time(Left(newField, 5), 'h:mm: TT') as Time,

  SubField(SubField(newField, '(', 1), ':', 3) as [in/out],

  SubField(SubField(newField, '(', 2), ')', 1) as door

;

LOAD

  SubField(myField, ',') as newField

;

LOAD

  @1 as myField

FROM

Data.csv

(txt, codepage is 1252, explicit labels, delimiter is '\t', msq, no eof)

;

Kindly find attached App.

Kind regards,

Ishfaque Ahmed

[maxgro]

From this we need to identify the time interval in minutes a particular student is in and out.

here for eg the IN period is 7 hrs and 35 minutes and out period is 1 hr 25 minutes.I tried subfield function but first row is getting empty and also error in row wise calculation.I am not able to get peek function,am using qlikview version 12.Thank you for your time.

I added some rows to the Ishfaque (thanks) script

Script

TMP:

LOAD

  Time(Left(newField, 5), 'h:mm: TT') as Time,

  SubField(SubField(newField, '(', 1), ':', 3) as [in/out],

  SubField(SubField(newField, '(', 2), ')', 1) as door

;

LOAD

  SubField(myField, ',') as newField

;

LOAD

  @1 as myField

FROM

Data.csv

(txt, codepage is 1252, explicit labels, delimiter is '\t', msq, no eof)

;

FINAL:

LOAD

  *,

  interval(Time - Peek('Time')) as DeltaTime

Resident TMP

order by Time;

DROP Table TMP;

  /* if you have many records, you need some change in DeltaTime calculation and order by) */

Result

Expression in textbox

=Sum({$ <[in/out]={out}>} DeltaTime)          in period

=Sum({$ <[in/out]={in}>} DeltaTime)            out period

[#4]

Dear Ishfaque,

Thanks for your prompt reply.Since I am new to qlik,I am sorry to say that I am unable to follow the steps instructed by you.My data model looks like given below

LOAD

studentid,

studentname,

punchrecords;

SQL SELECT *FROM TIMETRACKER.DB;

So I tried to load punchrecords individually using load statement but its showing error.Then I tried like the one below but I am not getting the data fields such as newField, [in/out],door etc.

LOAD

studentid,

studentname,

punchrecords as myField;

SQL SELECT *FROM TIMETRACKER.DB;

LOAD

     SubField(myField, ',') as newField,

     Time(Left(newField, 5), 'h:mm: TT') as Time,

     SubField(SubField(newField, '(', 1), ':', 3) as [in/out],

     SubField(SubField(newField, '(', 2), ')', 1) as door

;

Is it possible to manipulate the calculations through expressions rather than in script?Also since I am using personal edition I cannot load the attached qvw files.Thank you for your time and awaiting your valuable suggestions.

Regards,

Rishi

[#5]

Thank you maxgro for your response,But I am sorry to say that I am unable to get through since I cannot load punchrecords.csv from my existing data model.I have replied to Ishfaque regarding the same.Hope you can get me any alternate solution.Thank you for your time

Regards

Rishi

[engishfaque]

Dear Rishi,

Kindly use listed below data model,

TMP:

LOAD studentid,

     studentname

     Time(Left(newField, 5), 'h:mm: TT') as Time,

     SubField(SubField(newField, '(', 1), ':', 3) as [in/out],

     SubField(SubField(newField, '(', 2), ')', 1) as door

;

LOAD studentid,

     studentname

     SubField(myField, ',') as newField

;

LOAD

  studentid,

  studentname,

  punchrecords as myField

;

SQL SELECT *FROM TIMETRACKER.DB;

FINAL:

LOAD

  *,

  interval(Time - Peek('Time')) as DeltaTime

Resident TMP

order by Time;

DROP Table TMP;

Please note, if it is possible please share us actual data which is coming in studentid, studentname and punchcords fields at least 2 or 3 records as we can see that what is coming in your actual fields.

Kind regards,

Ishfaque Ahmed

[#7]

Dear Ishfaque,

Thanks for your quick response.I apologize for the mistake from my side as I didnt give relevant information.Apart from those listed above I have few other data fields like earlyby,lateby,etc.I need to load them as well along with these data.I tried the solution given by you but I could not load other fields.So please tell me how to write data model so that I can load rest of the fields as well and display the details of a studentid in a straight table with studentid as dimension.Should I write conditional dimension?

Thank you for your time and awaiting your valuable suggestions.

Regards

Rishi

Studentid	StudentName	AttendanceDate	InTime	OutTime	PunchRecords
2928	Neha	25/11/2015	10:10	17:56	10:10:in(FD),10:45:out(FD),11:14:in(FD),12:42:out(BD),12:55:in(BD),14:06:out(BD),14:24:in(BD),14:41:out(FD),14:57:in(FD),16:39:out(BD),16:54:in(BD),17:56:out(FD)
2929	Gireesh	25/11/2015	10:03	19:01	10:03:in(FD),11:14:in(FD),12:42:out(FD),12:55:in(FD),14:07:in(BD),14:07:out(BD),14:23:in(BD),14:31:out(FD),16:01:out(BD),16:02:in(BD),16:54:in(FD),19:01:out(FD)
2882	Sara	25/11/2015	9:06	18:36	09:06:in(FD),12:54:out(BD),13:27:in(BD),13:39:out(FD),13:45:in(FD),16:12:out(FD),16:16:in(FD),18:36:out(FD)

[maxgro · Accepted solution]

this is using your data in an inline load (bold)

S:

load * inline [

Studentid StudentName AttendanceDate InTime OutTime PunchRecords

2928 Neha 25/11/2015 10:10 17:56 10:10:in(FD),10:45:out(FD),11:14:in(FD),12:42:out(BD),12:55:in(BD),14:06:out(BD),14:24:in(BD),14:41:out(FD),14:57:in(FD),16:39:out(BD),16:54:in(BD),17:56:out(FD)

2929 Gireesh 25/11/2015 10:03 19:01 10:03:in(FD),11:14:in(FD),12:42:out(FD),12:55:in(FD),14:07:in(BD),14:07:out(BD),14:23:in(BD),14:31:out(FD),16:01:out(BD),16:02:in(BD),16:54:in(FD),19:01:out(FD)

2882 Sara 25/11/2015 9:06 18:36 09:06:in(FD),12:54:out(BD),13:27:in(BD),13:39:out(FD),13:45:in(FD),16:12:out(FD),16:16:in(FD),18:36:out(FD)

] (delimiter is '\t');

TMP:

LOAD

  *,

  Time(Left(newField, 5), 'h:mm: TT') as Time,

  SubField(SubField(newField, '(', 1), ':', 3) as [in/out],

  SubField(SubField(newField, '(', 2), ')', 1) as door

;

LOAD

  Studentid & '-' & AttendanceDate as StudentDate,

  Studentid,

  StudentName,

  AttendanceDate,

  InTime,

  OutTime,

  PunchRecords,

  subfield(PunchRecords, ',') as newField

Resident S;

DROP Table S;

FINAL:

LOAD

  *,

  if(StudentDate = peek('StudentDate'), interval(Time - Peek('Time')), 0) as DeltaTime

Resident TMP

order by Studentid, AttendanceDate, Time;

DROP Table TMP;

result (using the same chart of my previous post) is

Then replace the load inline (bold) with a load from your database, something like

S:

LOAD

    ....

    ;

SQL SELECT

    Studentid, StudentName, AttendanceDate, InTime, OutTime, PunchRecords

FROM TIMETRACKER.DB;

[#9]

Dear Maxgro,

Thank you for your time and response.The solution what I required was the chart posted by you earlier but with minor changes updated in this post