14 Replies Latest reply: Jul 12, 2011 5:19 AM by Anne Duffy RSS

    Partial Load from script

      Hi guys

       

      I have a QV doc, it is fed from a spreadsheet .

       

      When I reload my QV doc everymorning it automatically updates 5 field from this spreadsheet, Date, Time , Code, Number and Value.

      Fine and no worries.

       

      But througout the day the team uses this doc and deletes the days previous activities and updates with "todays", so tomorrow morning if I reload QV I have lost the data from the day previous.

       

      Is there a function in the script where it will retain the field values and just "Add on" the new data ???

       

      Please help

       

       

      Thanks

      A

        • Partial Load from script
          Kaushik Solanki

          Hi,

           

             You can do one thing create a QVD out of it.

           

              Update Qvd daily, with incremental load.

           

              Then use QVD as your data source.

           

          Regards,

          Kaushik Solanki

            • Partial Load from script

              Hi Kaushik,

               

              Thanks for your response, but I'm not too sure what you mean ?

               

              Can you please give me a little more detail ?

               

              Thanks

              A

                • Partial Load from script
                  Kaushik Solanki

                  Hi,

                   

                     I am sorry for that.

                   

                     What i mean is, you can create a qvd that will hold the same data as your excel sheet.

                   

                     As you said your excel sheet gets refreshed with a new data daily and old data gets deleted.

                   

                    So what you can do is you can update qvd daily and use that qvd for developing your application.

                   

                    So in these way you will not loose the old data as that will be available in QVD.

                   

                    If possible i will upload a sample application.

                   

                  Regards,

                  Kaushik Solanki

                  • Re: Partial Load from script

                    Hi,

                    You can use partial load using ADD load. Just add keyword ADD in front of LOAD and do the Partial load using Ctr+Shift+R.

                    Assuming you want to keep older Qvd data as it is and want to update new data from newer file.

                     

                    If you use Reload, it'll overwrite QVW data. 

                     

                     

                    hope this gives you answer..

                     

                    Cheers

                    Deepak

                      • Partial Load from script


                        Sorry guys

                         

                        Ive been away from Qlikview for a bit , the above looks real helpful , only getting a chance to look at it now. Ill let you know how I go

                         

                        Thanks

                        Anne

                          • Partial Load from script

                            Hi Guys,

                             

                            I have been testing the above and just cant it to work ,

                             

                            For example, ( this is a log of things to do "TOMORROW")

                             

                            Yesterday the 07/07/10, I Reloaded my data and the information fed correctly. Throughout the day ithe Excel sheets data was updated to the 08/07/10.

                            This morning when I Reloaded my data I lost the previous Information from the 07/07/10 and retrived the data from the 08/07/10.

                             

                            However on advice from above I did change the load retrieval as a ADD Load.

                             

                            I have pasted the script below, Hopefully someone can help

                             

                            Thanks

                             

                            CONCATENATE (MasterTable)

                            Add LOAD Branch,

                            [Deal No],

                            [Rate Code],

                            [Posting Date],

                            Date(Date) as Date,

                            Month(Date(Date)) as Month,

                            Week(Date(Date)) as Week,

                            Year(Date(Date)) as Year,

                            [Mat Date],

                            Currency,

                            Amount,

                            Rate,

                            Opics,

                            '1' as Number,

                            mid(Opics,18,9) as [Customer Number],

                            Code,

                            Type,

                            [Branch code],

                            ApplyMap ('branchId',[Branch code]) as [Opics Team],

                            ApplyMap('numm',mid(Opics,18,9),'Text Update') as [Business Unit],

                            /*if(if(mid(Opics,6,2)='AL','Annuity',ApplyMap('ratm',[Rate Code]))='Structured','Structured Rollover','Unstructured Rollover') as Activity,*/

                            if(if(mid(Opics,6,2)='AL','Annuity',ApplyMap('ratm',[Rate Code]))='Unstructured','Unstructured Rollover','Structured Rollover') as Activity,

                            if(ApplyMap('numm',mid(Opics,18,9),'Text Update')='SYN','30',ApplyMap ('CycleTimesM',if(mid(Opics,6,2)='AL','Annuity',ApplyMap('ratm',[Rate Code])))) as [Time to complete],

                            ApplyMap ('FCBranMap',mid(Opics,18,9),null()) as [Flexcube Branch],

                            text ((ApplyMap ('FCBranMap',mid(Opics,18,9),null()))& text(ApplyMap('numm',mid(Opics,18,9),'Text Update'))) as Lookup,

                            ApplyMap ('AMmAP',text ((ApplyMap ('FCBranMap',mid(Opics,18,9),null()))& text(ApplyMap('numm',mid(Opics,18,9),'Text Update'))),'UDF Investigate') as [Assistant Manager],

                            applyMap ('mMap',ApplyMap ('AMmAP',text ((ApplyMap ('FCBranMap',mid(Opics,18,9),null()))& text(ApplyMap('numm',mid(Opics,18,9),'UDF Investigate'))),null())) as Manager,

                            if (Date-[Mat Date]=0, 'Y', 'N') as [Maturing Structured]

                            FROM   

                            [S:\Business Support\Qlikview\Preview\Report loads\Master.xls]

                            (biff, embedded labels, table is [Refresh GRSS$])

                            WHERE Date=Date(Today());

                              • Partial Load from script
                                Kaushik Solanki

                                HI,

                                 

                                   As i said earlier, you can create a qvw file (Qlikview Application) which will do the incremental load from your excel sheet daily.

                                 

                                   Go through the link below for Incremental Load.

                                 

                                   http://community.qlik.com/docs/DOC-1870

                                 

                                   Then you can use the qvd updated daily by Incremental load.

                                 

                                   This way your qvd will have history data as well, even tough it has been deleted from the excel.

                                 

                                Regards,

                                Kaushik Solanki

                                  • Partial Load from script

                                    Hi Kaushik

                                     

                                    I have been looking at the Incremental Loads and down loaded the Cookbook with sample QVD. For me, it is still reloading the data as whats in the Excel file at time of script ( even on the tester/sample documentation)

                                     

                                    I will keep working away at it and ill kerep you posted,

                                     

                                    Thanks again

                                    A

                                      • Re: Partial Load from script
                                        Miguel Angel Baeyens de Arce

                                        Hello Anne,

                                         

                                        Another probably simpler option to keep usin normal loads but still keep all records and probably easier to maitain is to do a two step load, concatenate and store. Meaning that you always load from the QVD (data already loaded and stored), concatenating with the excel spreadsheet and storing into the same QVD (so now you have yesterday and today information in the same QVD), and so.

                                         

                                        Excel: // Okay, this is not an actual excel with DAY 1 but let's pretend
                                        LOAD * INLINE [
                                        Date, Code, Value
                                        08/07/2010, D, 400
                                        08/07/2010, E, 500
                                        08/07/2010, F, 600
                                        ];
                                        // You already have a QVD storing DAY 2
                                        // There's no need to specify CONCATENATE, both QVD and Excel have same field name and number
                                        LOAD * 
                                        FROM Data.qvd (qvd);
                                        
                                        // Now you have both DAY 1 and DAY 2
                                        STORE Excel INTO Data.qvd;
                                        

                                         

                                        Next time the script loads (day 3) you get day 3 from excel (it's ok) and you load QVD with days 1 and 2 as well (it's ok no data loss) and store whole three days in the QVD you will use tomorrow. And so on.

                                         

                                        Now think of playing with variables to name the QVDs with dates, so you can keep track record of the last week, month or whatever in different QVDs, with no need to update the script to load them again.

                                         

                                        Actually this gives me more control of what I'm loading and where it is stored with a bit of additional code.

                                         

                                        Hope that makes sense.

                                         

                                        Miguel Angel Baeyens

                                        BI Consultant

                                        Comex Grupo Ibérica

                                          • Re: Partial Load from script

                                            Miguel

                                             

                                            This is very helpful thanks,

                                             

                                            Can I ask you have written an Inline table, is this where I put point to my Excel sheet that we are manually updating daily ?

                                            Thanks
                                            Anne

                                            • Partial Load from script

                                              Hi Miguel

                                               

                                              I think I understand what the script is to do,

                                               

                                              It takes the data and copies it into a QVD file , then it loads everything from this QVD file. Then loads from the file rather than the original data source.

                                               

                                              I have amended your query to fit my data but I seem to be getting 2 main problems,

                                               

                                              1. It does not seem to be able to loctae the QVD file, I have attached Sreen shot of error message,

                                              QVD error.bmp

                                               

                                              2. As I have been testing this for a couple of days now , with live data , it still seems to be overwriting, so It will load todays data and oversave data from the 8/07/11.

                                               

                                              I have pasted the script below, I think it may be as I am using a concatenate load, this is as if I dont I create loads of SYN Keys , perhaps you can advise if my understanding is correct and if there is something I cn do to fix,

                                               

                                              thanks a million

                                              A

                                              CONCATENATE (MasterTable)

                                              LOAD Branch,

                                              [Deal No],

                                              [Rate Code],

                                              [Posting Date],

                                              Date(Date) as Date,

                                              Month(Date(Date)) as Month,

                                              Week(Date(Date)) as Week,

                                              Year(Date(Date)) as Year,

                                              [Mat Date],

                                              Currency,

                                              Amount,

                                              Rate,

                                              Opics,

                                              '1' as Number,

                                              mid(Opics,18,9) as [Customer Number],

                                              Code,

                                              Type,

                                              [Branch code],

                                              ApplyMap ('branchId',[Branch code]) as [Opics Team],

                                              ApplyMap('numm',mid(Opics,18,9),'Text Update') as [Business Unit],

                                              /*if(if(mid(Opics,6,2)='AL','Annuity',ApplyMap('ratm',[Rate Code]))='Structured','Structured Rollover','Unstructured Rollover') as Activity,*/

                                              if(if(mid(Opics,6,2)='AL','Annuity',ApplyMap('ratm',[Rate Code]))='Unstructured','Unstructured Rollover','Structured Rollover') as Activity,

                                              if(ApplyMap('numm',mid(Opics,18,9),'Text Update')='SYN','30',ApplyMap ('CycleTimesM',if(mid(Opics,6,2)='AL','Annuity',ApplyMap('ratm',[Rate Code])))) as [Time to complete],

                                              ApplyMap ('FCBranMap',mid(Opics,18,9),null()) as [Flexcube Branch],

                                              text ((ApplyMap ('FCBranMap',mid(Opics,18,9),null()))& text(ApplyMap('numm',mid(Opics,18,9),'Text Update'))) as Lookup,

                                              ApplyMap ('AMmAP',text ((ApplyMap ('FCBranMap',mid(Opics,18,9),null()))& text(ApplyMap('numm',mid(Opics,18,9),'Text Update'))),'UDF Investigate') as [Assistant Manager],

                                              applyMap ('mMap',ApplyMap ('AMmAP',text ((ApplyMap ('FCBranMap',mid(Opics,18,9),null()))& text(ApplyMap('numm',mid(Opics,18,9),'UDF Investigate'))),null())) as Manager,

                                              if (Date-[Mat Date]=0, 'Y', 'N') as [Maturing Structured]

                                              FROM   

                                              [S:\Business Support\Qlikview\Preview\Report loads\Master.xls]

                                              (biff, embedded labels, table is [Refresh GRSS$])

                                              WHERE Date=Date(Today());

                                              // You already have a QVD storing DAY 2

                                              // There's no need to specify CONCATENATE, both QVD and Excel have same field name and number

                                              CONCATENATE (MasterTable)

                                              LOAD *

                                              FROM [S:\Business Support\Qlikview\Preview\Tables\Rollovers.qvd]

                                              ;

                                              // Now you have both DAY 1 and DAY 2

                                              STORE Excel INTO [S:\Business Support\Qlikview\Preview\Tables\Rollovers.qvd]

                                              ;

                                                • Re: Partial Load from script
                                                  Miguel Angel Baeyens de Arce

                                                  Hi,

                                                   

                                                  First of all, you don't have any table in your script above called "Excel", so that's why the STORE sentence returns an error. I'd say this table is called MasterTable but certainly there is something above that first line I can't see that is concatenated to the excel file. Is this the table you want to save?

                                                   

                                                  Second, a date is a numeric value, but when called with the function "Date" it returns a literal, so yor load of the Master.xls should read like this

                                                   

                                                  LET vToday = Date(Today()); // before any load
                                                  
                                                  LOAD .... // Your script here
                                                  FROM   
                                                  [S:\Business Support\Qlikview\Preview\Report loads\Master.xls]
                                                  (biff, embedded labels, table is [Refresh GRSS$])
                                                  WHERE Date = '$(vToday)'; // WHERE condition refers to the variable with the date, single quoted
                                                  

                                                   

                                                  This should be closer to what you want to get since you should only get values of today in your model, and the rest those that have been stored into the QVD file.

                                                   

                                                  Hope that helps.

                                                   

                                                  Miguel Angel Baeyens

                                                  BI Consultant

                                                  Comex Grupo Ibérica

                                                    • Re: Partial Load from script

                                                      Hi Miguel,

                                                       

                                                      I have like a 8 Tab script that makes ups the Master Table, however, I revert back to your original script and loaded as excel and changed my script load to avoid SYN keys,

                                                       

                                                      As this is taking data at value today , i will run tests daily to see if this works,

                                                       

                                                      Thanks again Miguel, youre very helpful in improving my understanding of this

                                                       

                                                      Kind regards

                                                      A