Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to load file with undesired format?

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.

sample verint.PNG

Is it possible to load the file with arranged format as below?

sample verint2.PNG

12 Replies
petter
Partner - Champion III
Partner - Champion III

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:

2018-04-12 10_23_46-_¤ Unmanaged Report _ Data load editor - Qlik Sense.png

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");

petter
Partner - Champion III
Partner - Champion III

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");

Anonymous
Not applicable
Author

Peterrrr,

It is now working! You're a genius... Super! thanks