5 Replies Latest reply: Jun 5, 2015 9:44 AM by Jonas Melo RSS

    Load function

    Martijn Noorda

      Dear Qlikview community,

       

      I have a question regarding the load function. What I would like to do is to load the most recent report in my script. I load multiple reports to create a dashboard that displays trade position reporting and feedback from the reports. Most reports are downloaded from the same portal. However, the portal is not always up to date. The reports are saved on the same location daily using the name of the report and the date of the report. This means I can use the date in the file name to load the most recent report.

       

      I would like to do something like this:

       

      load

       

      from (location)/namereport-mostrecentdate

       

      I figured there should be a way to script something like this. Besides this, I would like all reports to have the same reporting date. It is possible that some reports can be from yesterday, while others are older. In that case I would like all load statements to load the most recent reporting date for which all reports are availabe, which is probably harder to do. Can anyone provide code for this or steer me into the right direction?

       

      Thanks!

        • Re: Load function
          Gysbert Wassenaar

          You can find an answer in this post: Re: Re: Oldest Excel file LOADING

          • Re: Load function
            Jonas Melo

            You might like to also see this.

             

            SET v_path = '<servername>\QVD\';

             

            //the read of qvd's

            For Each ext in 'qvd','QVD'

              For Each file  in FileList(path&'*.'&ext)

              COLLECTION_QVD:

              LOAD

              '$(file)' as FilePath,

              FileTime('$(file)') as DateModify,

              Date(FileTime('$(file)')) as Date

              autogenerate 1;

              NEXT file

            NEXT ext

             

            STORE [COLLECTION_QVD] into [COLLECTION_QVD.qvd] (qvd);

            DROP Table COLLECTION_QVD;

             

            COLLECTION_QVD:

            LOAD FilePath,

                 DateFile

            FROM

            COLLECTION_QVD.qvd (qvd)

            where

            Date  >= MakeDate(Year(Now()-2),Month(Now()-2),Day(Now()-2)); //Clause of their choice.

            /*

            ...

            .

            .

            .

            Space load for their logic most recente date...

            .

            .

            .

            ...

            */

              • Re: Load function
                Martijn Noorda

                Thank you for your help. I have a better understanding of how I can load the latest file of a list, which I actually managed to do. I did the following

                 

                Set NullInterpret = '';

                SET ThousandSep='.';

                SET DecimalSep=',';

                SET MoneyThousandSep='.';

                SET MoneyDecimalSep=',';

                SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

                SET TimeFormat='h:mm:ss';

                SET DateFormat='MM-DD-YYYY';

                SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

                SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

                SET DayNames='ma;di;wo;do;vr;za;zo';

                LET vPath = 'C:\Users\yno\Documents\Qlikview-Dashboard\';

                LET vToday = date(today(),'DDMMYYYY');

                LET vPositions = 'OTC-Posities';

                LET vDTCCFX = 'DTCCFX';

                LET vDTCCRATES = 'DTCCRATES';

                LET vDTCCCOMMODITIES = 'DTCCCOMMODITIES';

                LET vDTCCCREDIT='DTCCCREDIT';

                 

                 

                datagathering:


                load date(filetime(),'DDMMYYYY') as Filetimestamp,

                FileName() as Filename,

                date(Mid(Filename(),findoneof(Filename(),'-')+1,10),'MM-DD-YYYY') as datum //this is the position of the date in the filename of the reports

                 

                from $(vPath)*.csv;


                load *


                resident datagathering

                order by datum;

                 

                load date(max(datum),'DDMMYYYY') as maximaal

                 

                resident datagathering;

                 

                LET vDatum=fieldvalue('maximaal',1);


                load *


                from $(vPath)$(vDatum)$(vDTCCFX).csv;

                 

                This allows me to load the latest file of each report. However, a problem arises when not all reports have the same date, this can actually happen, because I have noticed that sometimes certain reports aren't updated daily where others are. Since it is vital that all reports are from the same reporting date, I would like to use code to actually get the most recent date for which all reports for that date exist on the server. Do you have any suggestions for realizing this?

                  • Re: Load function
                    Peter Cammaert

                    Isn't that a bit dangrous? I mean, if you load only the latest reports in different tables, and some reports may not exist on that date, then certain tables won't be created and this may cause problems further in your script or in the UI.

                     

                    Or are you CONCATENATing all good csv files in a single large resident table? In that case you could use something like this instead of the last LOAD in your script:

                     

                    FOR each vInputTable IN '$(vDTCCFX'', '$(vDTCCRATES), ... // Add the others

                     

                      IF Not IsNull(FileTime('$(vPath)$(vDatum)$(vInputTable).csv')) THEN  // file exists

                        BigTable:  // Replace with [$(vInputTable)]: if you want separate tables

                        LOAD * FROM [$(vPath)$(vDatum)$(vInputTable).csv] (options);

                      END IF

                    NEXT

                     

                    Best,

                     

                    Peter

                    • Re: Load function
                      Jonas Melo

                      The important is define which is period most recent. After, create one work logic with this time, and use in where clause.

                       

                      Also, think of the idea of how to treat non existent report.

                       

                      Hope this helps!