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?
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");
Also if you want to bring in the two fields of Supervisor and Organization that are structured in the similar way as Start Date when it comes to placement over the lines the load script will look like this:
LOAD
"Start Date",
If(IsNum("Start Date") AND IsText(Previous("Start Date"))
,SubField(Previous("Start Date"),':',2)
,Peek('Employee'))
AS Employee,
If(IsNum("Start Date") AND IsText(Previous("Start Date"))
,SubField(Previous("Time Not Adhering to Schedule (Hours)"),':',2)
,Peek('Supervisor'))
AS Supervisor,
If(IsNum("Start Date") AND IsText(Previous("Start Date"))
,SubField(Previous("Time Adhering to Schedule (%)"),':',2)
,Peek('Organization'))
AS Organization,
"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");
Peterrrr,
It is now working! You're a genius... Super! thanks