8 Replies Latest reply: Sep 11, 2013 9:43 AM by Marcus Sommer RSS

    Load Excel sheet up to a fixed value

      Hi everyone,

      i'm new in QlikView and i've a problem with loading an Excel-Sheet(s):

      My datasource is an Excel file which contains 12 sheets (Januar - Dezemeber). In every sheet is a start value ("Datum") and an end value ("Durchschnitt"). How can I load all data in this sheets between this 2 keywords?

       

      My plan was to set 'header is 6 lines' and then to load until the value 'Durchschnitt' in the column 'F1 as Datum' appears.

       

      DO 
      Argus_Propan:
      LOAD F1 as Datum, 
           Low as CIF_ARA_LARGE_Low, 
           High as CIF_ARA_LARGE_High, 
           Mittelw. as CIF_ARA_LARGE_Avg, 
           [Mittelw. Kum.] as CIF_ARA_LARGE_Avg_Cum, 
           Low1 as FCA_ARA_RAIL_Low, 
           High1 as FCA_ARA_RAIL_High, 
           Mittelw.1 as FCA_ARA_RAIL_Avg, 
           [Mittelw. Kum.1] as FCA_ARA_RAIL_Avg_Cum, 
           Low2 as FOB_ARA_BARGE_Low, 
           High2 as FOB_ARA_BARGE_High, 
           Mittelw.2 as FOB_ARA_BARGE_Avg, 
           [Mittelw. Kum.2] as FOB_ARA_BARGE_Avg_Cum
      FROM
      [C:\Users\m006u021\Desktop\Argus Propan 2013.xls]
      (biff, embedded labels, header is 6 lines, table is September$);
      LOOP while (F1 = 'Durchschnitt');
      
      STORE Argus_Propan INTO $(vQVDPath)\Argus_Propan.qvd;
      DROP TABLE Argus_Propan;
      

       

      Your help is much appreciated!

      Albert E.

        • Re: Load Excel sheet up to a fixed value
          Erik van hout

          Hi Albert,

           

          When you're in the script editor and you've selected the Argus Propan 2013.xls file (using the Table Files... button in the script editor) you can enter the File Wizard (by selecting the Next> button).

          With a conditional delete you can leave out the row containing Durchschnitt.

           

          Hope this helps.

            • Re: Load Excel sheet up to a fixed value

              Hi Erik,

               

              thanks for your fast reply. I tried the Wizard already - thanks for this tip.

               

              But I forgot to mention that it should be kept variable and all the sheets are updated every day for the curent month yearly.

              If I use the wizard, i would have to generate the load every year new (Argus Propan 2014.xls) -> the number of data varies from month to month and from year to year - but the two conditions are always the same.
              My idea was it to go thorugh a sheet and check the start condition ('Datum') and the end condition ('Durchschnitt') for each month and load the range between this two conditions.


              Thanks & Best Regards,

              Albert

                • Re: Load Excel sheet up to a fixed value
                  Erik van hout

                  Hi Albert,

                   

                  With the conditional delete - the wizard will always look for the row containing the end condition Durchschnitt.

                  You can also use a conditional to delete the header rows (or you must make sure always the same amount of header rows are used).

                  All other rows will be added to your table.

                  You can use a variable to store the filename.

                  That way you only have to change the variable once a year.

                  You can even store this vaiable in a settings-file.

              • Re: Load Excel sheet up to a fixed value
                Peter Cammaert

                How about starting to read at row 8 and continue reading while the first column contains a value that can be interpreted as a numerical value using IsNum()? Internally Excel represents dates as the integer part of a floating point value. 

                 

                Luck,

                 

                Peter

                • Re: Load Excel sheet up to a fixed value

                  Thank you all for your help,

                   

                  i've done it like here

                  http://community.qlik.com/message/327956#327956

                  See last post from

                  Argus_Data:
                  LOAD F1 as Datum,
                       Low as CIF_ARA_LARGE_Low,
                       High as CIF_ARA_LARGE_High,
                       Mittelw. as CIF_ARA_LARGE_Avg,
                       [Mittelw. Kum.] as CIF_ARA_LARGE_Avg_Cum,
                       Low1 as FCA_ARA_RAIL_Low,
                       High1 as FCA_ARA_RAIL_High,
                       Mittelw.1 as FCA_ARA_RAIL_Avg,
                       [Mittelw. Kum.1] as FCA_ARA_RAIL_Avg_Cum,
                       Low2 as FOB_ARA_BARGE_Low,
                       High2 as FOB_ARA_BARGE_High,
                       Mittelw.2 as FOB_ARA_BARGE_Avg,
                       [Mittelw. Kum.2] as FOB_ARA_BARGE_Avg_Cum,
                       RecNo() as Range
                  FROM
                  [C:\Users\m006u021\Desktop\Argus Propan 2013.xls]
                  (biff, embedded labels, header is 5 lines, table is September$);
                  
                  
                  max:
                  load max(Range) as ad Resident Argus_Data;
                  let b=Peek('ad',0);
                  drop Table max;
                  let c = Lookup('Range','Datum','Durchschnitt','Argus_Data');
                  
                  Argus_Propan:
                  NoConcatenate
                  LOAD * Resident Argus_Data Where Range < $(c); //and no< $(d);
                  
                  
                  
                  STORE Argus_Propan INTO $(vQVDPath)\Argus_Propan.qvd;
                  DROP TABLE Argus_Data, Argus_Propan;
                  
                  

                   

                   

                  Thanks & Best Regards,

                  Albert

                  • Re: Load Excel sheet up to a fixed value
                    Marcus Sommer

                    I suggest you could it better load with an approach like this:

                     

                    for each vTabSheet in 'Januar', 'Februar', ... and so on

                         Argus_Propan:

                         first 31 // number of max. rows

                         Load ...

                         From [C:\Users\m006u021\Desktop\Argus Propan 2013.xls] 

                         (biff, embedded labels, header is 8 lines, table is $(vTabSheet))

                         Where isnum(F1);

                    next


                    store ...

                    drop ...


                    Your table will be a crosstable - perhaps it is a better way this table to convert in a normal table. You could here use the crosstable-wizard. All parts from this suggestion you could find in the manual.

                     

                    - Marcus