Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eustisab
New Contributor III

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

1 Solution

Accepted Solutions
MVP
MVP

Re: how to load file with undesired format?

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

12 Replies

Re: how to load file with undesired format?

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;

Capture.PNG

MVP
MVP

Re: how to load file with undesired format?

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:

2018-04-04 08_20_39-Edit reply to Re_ how to load file with undesi... _ Qlik Community.png

sasiparupudi1
Honored Contributor III

Re: how to load file with undesired format?

Another solution is

T1:

LOAD

[Start Date],

If(Index([Start Date],'Employee:'),1,0) As HeaderLine,

[Time Adhering to Schedule (Hours)],

[Total Time Scheduled (Hours)],

[Time Adhering to Schedule (%)]

FROM

[sample verint report.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Where

(Index([Start Date],'Combined Totals:')=0 And Index(Trim([Start Date]),'Totals:')=0)

;

T2:

NoConcatenate LOAD

[Start Date],

If(HeaderLine=1,SubField([Start Date],':',2),Peek(Employee)) As Employee ,

[Time Adhering to Schedule (Hours)],

[Total Time Scheduled (Hours)],

[Time Adhering to Schedule (%)],

HeaderLine

Resident T1

    ;

Drop Table T1;

Final:

NoConcatenate LOAD

*

Resident

T2

Where

HeaderLine=0

;

Drop Table T2;

eustisab
New Contributor III

Re: how to load file with undesired format?

Hi Petter,

It is weird that I'm getting this error.

error start date.png

eustisab
New Contributor III

Re: how to load file with undesired format?

Hi Sunny,

I'm also getting this error.

error start date.png

MVP
MVP

Re: how to load file with undesired format?

It seems like the CSV-file doesn't have the field Start Date....

eustisab
New Contributor III

Re: how to load file with undesired format?

but it has "Start Date" field..

start date field error.PNG

MVP
MVP

Re: how to load file with undesired format?

The load script I provided works still with the CSV-file you provided earlier - so something must have changed with the file you are now trying to load.

Could you upload your current CSV-file now and share it with us?

eustisab
New Contributor III

Re: how to load file with undesired format?

Here's the current CSV file to load that need to arrange the format.

Unmanaged report 2018-04-03_04-13-33_550.csv

Community Browser