Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This is the file that I want to load in Qlik. However, the format of it will not give me best analysis to show in the visualization.
Is it possible to load the file with arranged format as below?
Accepted Solutions
When bringing in the file you will have to ignore the first few lines since they are really just top header report information and not part of the actual table you want to bring in. Here is how you need to specify that in Qlik Sense:
You can also just modify this in the already existing load script if you want:
LOAD
"Start Date",
If(IsNum("Start Date") AND IsText(Previous("Start Date"))
,SubField(Previous("Start Date"),':',2)
,Peek('Employee'))
AS Employee,
"Time Adhering to Schedule (Hours)",
"Time Not Adhering to Schedule (Hours)",
"Total Time Scheduled (Hours)",
"Time Adhering to Schedule (%)",
"Time Not Adhering to Schedule (%)",
"Adherence Violations"
FROM [lib://CSV]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq, header is 7 lines)
WHERE
IsNum("Start Date");
Try this
Table:
LOAD SubField([Start Date], ': ', -1) as Employee,
RowNo() as RowNum
FROM
[..\..\..\Downloads\sample verint report.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Where WildMatch([Start Date], '*Employee*');
Table2:
LOAD [Start Date],
[Time Adhering to Schedule (Hours)],
[Total Time Scheduled (Hours)],
[Time Adhering to Schedule (%)],
If(WildMatch([Start Date], '*Total*'), RangeSum(Peek('RowNum1'), 1), Alt(Peek('RowNum1'), 1)) as RowNum1
FROM
[..\..\..\Downloads\sample verint report.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Where not WildMatch([Start Date], '*Employee*');
Left Join (Table)
LOAD RowNum1 as RowNum,
[Start Date] as Date,
[Time Adhering to Schedule (Hours)],
[Total Time Scheduled (Hours)],
[Time Adhering to Schedule (%)]
Resident Table2
Where not WildMatch([Start Date], '*Total*');
DROP Table Table2;
With this load script:
TIMESHEET:
LOAD
"Start Date",
If(IsNum("Start Date") AND IsText(Previous("Start Date"))
,SubField(Previous("Start Date"),':',2)
,Peek('Employee'))
AS Employee,
"Time Adhering to Schedule (Hours)",
"Total Time Scheduled (Hours)",
"Time Adhering to Schedule (%)"
FROM [lib://Emps/sample verint report.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq)
WHERE
IsNum("Start Date");
You will get the Employee Name on each row:
Hi Petter,
It is weird that I'm getting this error.
Hi Sunny,
I'm also getting this error.
but it has "Start Date" field.. 
