6 Replies Latest reply: Oct 16, 2017 3:53 PM by David Strong RSS

    Need some help with Load Script

    David Strong

      I am fairly new to Load Script for QlikView and need some help with one item.

       

      We are loading data as seen in my attachment. It has several rows of data with three columns, Business Unit, # of Invoices and TAT Result.

       

      There is a part of the load script that I need to update to pull in just the first row of the data (row 2 of the CSV file).

       

      This is the scrip that i have now and it is pulling in all the rows and adding them together for the TAT Result column. I am trying to use something similar to a Where clause or a SUMIF.

       

      ,Sum(IF( len(trim([TAT_Result]))>0 and  ApplyMap('mapSourceFiles', SourceFileID)='SLA 6.1', [TAT_Result]))as SLA6.1.Result
      ,Sum(IF(ApplyMap('mapSourceFiles', SourceFileID)='SLA 6.1', RecordCounter.Facts))
      /Sum(IF(ApplyMap('mapSourceFiles', SourceFileID)='SLA 6.1', RecordCounter.Facts))as SLA6.1.Flag

      ! !

      Any suggestions would be greatly appreciated.

        • Re: Need some help with Load Script
          Ralf Narfeldt

          David, I only see part of the script and it looks a bit mangled.

          But in general, to read a single record, you add

          WHERE(RowNo() < 1 )

          at the end of the Load clause.

          • Re: Need some help with Load Script
            sravanthi aluvala

            Hi,

            Try this,

             

            By using First n we can load n no. of rows from data.

             

            Example:

            First 1 LOAD * INLINE [

            Category,Value

            Education,800

            Entertainment,500

            Leadership,900

            ];

             

            It will give only first row from the table.

            10.PNG

            • Re: Need some help with Load Script
              Rahul Pawar

              Hello David,

               

              If you want to pull in just the first row of the data (row 2 of the CSV file) then you can try adding below code snippet to filter the records:

               

              WHERE RecNo()= 1;

               

              P.S.: Fields SLA6.1.Result and SLA6.1.Flag are ambiguous to me. If possible could you please elaborate more on this.

               

               

              Regards!

              Rahul

                • Re: Need some help with Load Script
                  David Strong

                  2017-02-04_19-26-22.png

                   

                  This is what is in my load script. The SLA6.1.Result and SLA6.1.Flag are pulled into the dashboard to display the results. I am pretty new to load scripting and am taking over this project from someone else.

                   

                  Rahul, how do I add in your Where clause to this script. I am sorry, I am still learning this.

                    • Re: Need some help with Load Script
                      Rahul Pawar

                      Hello David,

                       

                      If you have a table load as below:

                       

                      Data:
                      LOAD Column1,
                          Column2,
                          Column3,
                          Column4
                      FROM
                      [Data\SampleData.xlsx]
                      (ooxml, embedded labels, table is Sheet1);
                      
                      

                       

                      If you would like to filter it by adding filter based on RecNo() function then refer below modified code:

                       

                      Data:
                      LOAD Column1,
                          Column2,
                          Column3,
                          Column4
                      FROM
                      [Data\SampleData.xlsx]
                      (ooxml, embedded labels, table is Sheet1)
                      WHERE RecNo()= 1;
                      

                       

                      Hope this will help.

                       

                      Regards!

                      Rahul

                  • Re: Need some help with Load Script
                    David Strong

                    These are all good ideas, but still not ones that I think can help me here.

                     

                     

                    Perhaps some more context will help. This Load Statement is loading several other elements as well  and what I am needing is just the first row from one of the many items. So to add WHERE RecNo()= 1;  at the end of my Load Script would result in just the first record from each of the elements being loaded.

                     

                     

                    I need something like a Vlookup to the match to the specific row of the data.

                     

                     

                    I have made this work on the visualization side of things using this code:

                    IF([BUSINESS UNIT]='MyBuNAME',NUM(AVG({$<[Source File]={'SLA 6.1'},[BUSINESS UNIT]={'MyBuNAME'}>}[TAT_Result]), '0.00'), NUM(AVG({$<[Source File]={'SLA 6.1'},[BUSINESS UNIT]-={'MyBuNAME'}>}[TAT_Result]), '0.00'))

                     

                    MyBuNAME replaces the actual name in the script.

                     

                    If there is anyone who can offer up some help on this, I would be very grateful.