12 Replies Latest reply: Apr 12, 2018 10:23 PM by Isabel Encinas RSS

    how to load file with undesired format?

    Isabel Encinas

      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

        • Re: how to load file with undesired format?
          Sunny Talwar

          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

          • Re: how to load file with undesired format?
            Petter Skjolden

            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

            • Re: how to load file with undesired format?
              Sasidhar Parupudi

              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;

              • Re: how to load file with undesired format?
                Isabel Encinas

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

                 

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

                  • Re: how to load file with undesired format?
                    Petter Skjolden

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

                    • Re: how to load file with undesired format?
                      Petter Skjolden

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