topic Reading and Analysing csv files in QlikView

Reading and Analysing csv files

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Reading and Analysing csv files

engishfaque — Tue, 17 May 2016 18:24:56 GMT

!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

Re: Reading and Analysing csv files

maxgro — Tue, 17 May 2016 19:06:31 GMT

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

Re: Reading and Analysing csv files

Wed, 18 May 2016 07:42:18 GMT

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

Re: Reading and Analysing csv files

Wed, 18 May 2016 07:46:55 GMT

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

Re: Reading and Analysing csv files

engishfaque — Wed, 18 May 2016 09:27:04 GMT

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

Re: Reading and Analysing csv files

Wed, 18 May 2016 10:56:15 GMT

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)

Re: Reading and Analysing csv files

maxgro — Wed, 18 May 2016 19:23:36 GMT

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

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

LOAD

....

;

SQL SELECT

Studentid, StudentName, AttendanceDate, InTime, OutTime, PunchRecords

FROM TIMETRACKER.DB;

Re: Reading and Analysing csv files

Thu, 19 May 2016 09:59:05 GMT

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