62 Replies Latest reply: Feb 5, 2014 8:03 AM by Srikanth P RSS
      • Incremental reload

        Hi,

        Yes, you can do the incremental load in db.

        Pre-requirement - Should have a primary key / Composite key and date field.

        3 types of Incremental load
        1. Only Insertion in db
        2. Insertion & updation in db
        3. Insertion, Updation & Physical Deletion in db

        Eg: P1 as Primary Key, D1 as Date Field

        For Only Insertion:

        XYZ:
        Load *;
        SQL Select a,b,c from xyz where D1>"Yesterday's Date"
        Concatenate
        Load a,b,c from XYZ.QVD;

        Store XYZ into XYZ.QVD;

        For Insertion and updation

        XYZ:
        Load *;
        SQL Select P1,a,b,c from xyz where D1>"Yesterday's Date"
        Concatenate
        Load P1,a,b,c from XYZ.QVD where NOT EXISTS(P1,P1);

        For Insertion, updation and deletion:

        XYZ:
        Load *;
        SQL Select P1,a,b,c from xyz where D1>"Yesterday's Date"
        Concatenate
        Load P1,a,b,c from XYZ.QVD where NOT EXISTS(P1,P1);
        InnerJoin
        SQL Select P1 from xyz;

        Regards

        Rajesh

          • Incremental reload

             


            Rajesh Jeyaraman wrote:
            Hi,
            Yes, you can do the incremental load in db.
            Pre-requirement - Should have a primary key / Composite key and date field.
            3 types of Incremental load
            1. Only Insertion in db
            2. Insertion & updation in db
            3. Insertion, Updation & Physical Deletion in db
            Eg: P1 as Primary Key, D1 as Date Field
            For Only Insertion:
            XYZ:
            Load *;
            SQL Select a,b,c from xyz where D1>"Yesterday's Date"
            Concatenate
            Load a,b,c from XYZ.QVD;
            Store XYZ into XYZ.QVD;
            For Insertion and updation
            XYZ:
            Load *;
            SQL Select P1,a,b,c from xyz where D1>"Yesterday's Date"
            Concatenate
            Load P1,a,b,c from XYZ.QVD where NOT EXISTS(P1,P1);
            For Insertion, updation and deletion:
            XYZ:
            Load *;
            SQL Select P1,a,b,c from xyz where D1>"Yesterday's Date"
            Concatenate
            Load P1,a,b,c from XYZ.QVD where NOT EXISTS(P1,P1);
            InnerJoin
            SQL Select P1 from xyz;
            Regards
            Rajesh<div></div>


            In all your example you are verifying the data with the DATE("Yesterday's Date") field. Is there are any other way to do so if we don't have the date field. As I don't have the date field in my table. But I want to do the incremental load. Is that possible? Please let me know.

            Thanks and Regards,

            Rikab

             

          • Incremental reload

            Hi,

            It is bit difficult to find the inserted or updated records without the date field.

            In my previous mail the date field that i am pointing to record created or updated date field.

            Regards

            Rajesh

              • Incremental reload
                whiterabbit

                Thanks,

                I was just wondering if there is a way to do the incremental update without checking from the db which data sets are already imported. But it seems that there is no way to get around this...

                  • Incremental reload
                    Or Shoham

                     


                    whiterabbit wrote:I was just wondering if there is a way to do the incremental update without checking from the db which data sets are already imported. But it seems that there is no way to get around this...


                    Answering a question with a question - QlikView aside, how would you query the table only for records that were not previously imported, e.g. records that have changed (or been added) since the last time you run the query? If there's any field you can use that indicates this, you can use that for QlikView as well - but if your data does not in any way reflect changes, there's no way for QlikView to know what needs to be updated.

                     

                    One thing you can do instead of an incremental update is to load X days back and concatenate that with the contents of a QVD - assuming your records stop changing after a certain amount of time, of course. For example:

                    LET DATE_START = DATE((TODAY()-30));

                    myData:

                    SELECT * FROM myTable WHERE CreateDate>='$(DATE_START)';

                    Load * FROM myQVD.qvd WHERE CreateDate < '$(DATE_START)';

                     

                    store * myData into myQVD.qvd;

                     

                    Hope this helps.

                      • Incremental reload
                        whiterabbit

                        Now I added a datefield "MODTIME" which containts the timestamp when the data is created as value.
                        In the loadscript of QV app I have:

                        ...

                        LET LastExecTime = ReloadTime();


                        myData:
                        SELECT * FROM ORDERS
                        WHERE MODTIME > TO_TIMESTAMP('$(LastExecTime)','MM/DD/YY HH:MI:SS');
                        LOAD * FROM myQVD.qvd;
                        store * from myData into myQVD.qvd;

                        When I reload the data, the values stored in the qvd file are not loaded correctly (they are not shown, only the data added in the db is shown). Instead of displaying the data of myQVD there appears a field with xml. What I am doing wrong in the above statements??

                        Moreover, the first time I executed it, there was also an error because the qvd file does not yet exist... How can I handle this.

                          • Incremental reload
                            John Witherspoon

                            Something like this (untested, probably a couple syntax errors at a minimum):

                            IF filesize('myQVD.qvd') > 0 THEN
                            MaxTimeTable:
                            LOAD max(MODTIME) as MaxTime FROM myQVD.qvd (QVD);
                            MyData:
                            LOAD *;
                            SQL SELECT * FROM ORDERS
                            WHERE MODTIME > TO_TIMESTAMP($(=fieldvalue('MaxTime',1)),'MM/DD/YY HH:MI:SS');
                            DROP TABLE MaxTimeTable;
                            CONCATENATE (MyData) LOAD * FROM myQVD.qvd (QVD);
                            ELSE
                            MyData:
                            LOAD *;
                            SQL SELECT * FROM ORDERS;
                            END-IF

                            STORE MyData INTO myQVD.qvd;

                            • Incremental reload
                              Rob Wunderlich

                               


                              whiterabbit wrote:Instead of displaying the data of myQVD there appears a field with xml


                              Because the (qvd) parameter is missing in the QVD load.

                              LOAD * FROM myQVD.qvd (qvd)

                              -Rob

                                • Incremental reload

                                  Hi Rob,

                                  Can you please attach any sample application which does he incremental load please.

                                   

                                    • Incremental reload
                                      Rob Wunderlich

                                       


                                      Rikab Kothari wrote:Can you please attach any sample application which does he incremental load please.


                                      There is an example in the Qlikview Cookbook.

                                      -Rob

                                        • Incremental reload
                                          Neetu Singh

                                          Hi Rob,

                                           

                                           

                                          Here is the attached application for incremental load. plz find it.

                                           

                                          Hope it will solve your problem.

                                           

                                           

                                          Thanks

                                          Neetu Singh

                                           

                                            • Incremental reload

                                              Hi Neetu,

                                              It was not Rob. It was me who was looking for the incremental reload.

                                              Anyway many thanks for sending me the application. Hope it is understandable and can use the same logic in my document. Because I have few document which does the incremental load but I couldn't understand how it was done. Hope your document will be understandable.

                                               

                                                • Incremental reload
                                                  Rob Wunderlich

                                                   


                                                  Rikab Kothari wrote: It was me who was looking for the incremental reload


                                                  Hi Rikab,

                                                  There is another example in the Qlikview Cookbook that takes a slightly different approach then Neetu's example. You can download the Cookbook from

                                                  http://robwunderlich.com/Download.html

                                                  -Rob

                                                    • Incremental reload

                                                      Hi Rob,

                                                      Thanks for reply! Yes I already gone through that. But not able to understand how it works. If I don't understand then it not easy to apply the same logic in my document also.

                                                      May be I need to dig little deeper to find out how exactly it is happening there. I will definitely go through it once more then will let you know the outcome. Again many thanks for your concern!

                                                       

                                                       

                                                       

                                                       

                                                       

                                                      • Incremental reload

                                                        Hi Rob and All(Who are all is involved in this post),

                                                        I have attached very basic document to work on with incremental load. Can some one please do the incremental load with that so that I can understand the basic things in that.

                                                        It is very small and it will not take much time to work on it.

                                                        Hope to receive reply from some one soon.

                                                         

                                                         

                                                        • Incremental reload

                                                          Hi All,

                                                          Can some please help me out to do the incremental load for the document which is attached just for the understanding purpose. I have been waiting for the long time but still there is no response.

                                                           

                                                           

                                                           

                                                            • Incremental reload
                                                              Neetu Singh

                                                              Hi Rikab,

                                                               

                                                               

                                                              Please find the attached application for incremental load. hope it will helpful to you.

                                                               

                                                              If any issue . Plz feel free to coordinate with me,

                                                               

                                                              Bye nd Take Care

                                                              Neetu Singh

                                                                • Incremental reload

                                                                  Hey Neetu,

                                                                  Many thanks for the document but I have already seen that which has been sent by you only. I am not able to understand how increment load works in it. That's the reason I have attached one small document in it to make me understand. Requesting you to work on it and provide me some solution.

                                                                   

                                                                  • Re: Incremental reload

                                                                    Hi Neetu Singh,

                                                                     

                                                                    I just gone through the attached rar file but i need small clarification on reloading part.If Qvd exists the condition goes to QVD and fetch the max date from the date field and does the next reload based on that date,other wise it take full reload rt?Here my concern is,is it possible to fetch last six months data instead of full reload ?If aware,please ping answer on this query.

                                                                     

                                                                    Thanks in Advance

                                                                  • Incremental reload

                                                                     

                                                                    Hi Rikab,

                                                                    3 Types of Incremental Loads:

                                                                    1. Only Insertion in the table

                                                                    2. Insertion & updation in the table

                                                                    3. Insertion, Updation & Deletion in the table

                                                                    P1 - Primary Key 1

                                                                    C1 - Column 1

                                                                    C2 - Column 2

                                                                    D1 - Date Field 1

                                                                    Only Insertion in the table:

                                                                    Let say there is only insertion of record is happing in a table (T1). Now you want to do the incremental load for the table. You need to have a date field (D1) in the table (T1);

                                                                    First step before doing a incremental load is to get the current snap shot of the table and convert into QVD. Pls find the eg code for the above situation;

                                                                    T1:

                                                                    SQL Select P1, C1, C2, D1 from T1 where D1= "Yesterday's Date"; à This set has only yesterday's data retrieved from db.

                                                                    Load P1, C1, C2, D1 from T1.QVD where D1<"Yesterday's Date"; à This set has data less than the yesterday's date.

                                                                    Store T1 as T1.QVD à Now the T1 QVD has full set of data until yesterday.

                                                                    Insertion & Updation:

                                                                    Let say there is insertion & updation of record is happing in a table (T1). Now you want to do the incremental load for the table. You need to have a primary (P1) and date field (D1) in the table (T1);

                                                                    First step before doing a incremental load is to get the current snap shot of the table and convert into QVD. Pls find the eg code for the above situation;

                                                                    T1:

                                                                    SQL Select P1, C1, C2, D1 from T1 where D1= "Yesterday's Date"; à This set has yesterday's both inserted & updated data retrieved from db. Here the date field should affect by both insertion & updation of the record in the db.

                                                                    Load P1, C1, C2, D1 from T1.QVD where D1<"Yesterday's Date" and not exists(P1,P1); à This set has data less than the yesterday's date but not the updated records (updated records are not available in this set because of NOT EXISTS command).

                                                                    Store T1 as T1.QVD à Now the T1 QVD has full set of inserted & updated data until yesterday.

                                                                     

                                                                    Insertion, Updation & Deletion:

                                                                    Let say there is insertion, updation & physical deletion of record is happing in a table (T1). Now you want to do the incremental load for the table. You need to have a primary (P1) and date field (D1) in the table (T1);

                                                                    First step before doing a incremental load is to get the current snap shot of the table and convert into QVD. Pls find the eg code for the above situation;

                                                                    T1:

                                                                    SQL Select P1, C1, C2, D1 from T1 where D1= "Yesterday's Date"; à This set has yesterday's both inserted & updated data retrieved from db. Here the date field should affect by both insertion & updation of the record in the db.

                                                                    Load P1, C1, C2, D1 from T1.QVD where D1<"Yesterday's Date" and not exists(P1,P1); à This set has data less than the yesterday's date but not the updated records (updated records are not available in this set because of NOT EXISTS command).

                                                                    Inner Join SQL Selection P1 from T1; à This command executes only current available P1 records. Where it removes the records from above loaded Table T1 if it is not available in db.

                                                                    Store T1 as T1.QVD à Now the T1 QVD has full set of inserted & updated data until yesterday.

                                                                    I hope the above to understand better.

                                                                     



                                                                     

                                                                      • Incremental reload

                                                                        Hi Rajesh,

                                                                        Hope I am understanding your terms correctly.

                                                                         

                                                                        1. Only Insertion in the table --> Incremental load will happen only if any records are inserted in to the DB and will not happen if the data is updated or deleted.

                                                                        2. Insertion & updation in the table --> Incremental load will happen only if any new rows are inserted or if any new rows are updated.

                                                                        3. Insertion, Updation & Deletion in the table --> Incremental load will happen there is any new rows inserted,existing rows updated and existing rows deleted.

                                                                        Please correct me if I am wrong any where so that I can proceed further.

                                                                         



                                                                        • Incremental reload

                                                                          Hi Rajesh and Neetu,

                                                                          Requesting you to work on it so that it is understandable for me. I don't think it will take much time also as it has only one table in it.

                                                                          Please do the required changes in my document and attach it.

                                                                           

                                                                            • Incremental reload
                                                                              Neetu Singh

                                                                              Hi Rikab,

                                                                               

                                                                               

                                                                              I have uploaded your qlikview application and try to solve your query ASAP.

                                                                               

                                                                               

                                                                              Bye nd TC

                                                                              Neetu Singh

                                                                                • Incremental reload

                                                                                  Hi Neetu,

                                                                                  I think you have forgot attach the file. Requesting you to look reattach the file and send it to me.

                                                                                   

                                                                                  • Incremental reload
                                                                                    Neetu Singh

                                                                                    Hi Rikab,

                                                                                     

                                                                                     

                                                                                    Sorry for previous mail. Now, please find the attached file.

                                                                                      • Incremental reload

                                                                                        Hi Neetu,

                                                                                        Many thanks I will check whether it works as per my requirement or not. Hope you must be knowing my requirement. My requirement was do the incremental load with insert.update and delete.

                                                                                        Does the work done by you does all these requirement.

                                                                                         

                                                                                        • Incremental reload

                                                                                          Hi Neetu,

                                                                                          By the way I tried your application after inserting the data in to the excel sheet. But it seems there is no record getting inserted in to the qlikview. I have attached the entire folder again. The inserted record is the last row of the excel sheet. Please check!

                                                                                          • Incremental reload

                                                                                            Hi Neetu,

                                                                                            I checked the document which you have attached. It is not working as expected. Requesting you to have a look into it again and help me out.

                                                                                            Thanks and in advance

                                                                                              • Incremental reload
                                                                                                Neetu Singh

                                                                                                Hi,

                                                                                                 

                                                                                                 

                                                                                                OK fine, i will check it out because on my side it was working . i will correct it out and then send you again

                                                                                                • Incremental reload
                                                                                                  Neetu Singh

                                                                                                  Hi Rikab,

                                                                                                   

                                                                                                  I have check that code and now this one is perfectly working. So, please go through with this attached file.

                                                                                                   

                                                                                                  If there is any query then please feel free to mail me.

                                                                                                   

                                                                                                  Hope this one will help you

                                                                                                   

                                                                                                  Bye nd TC

                                                                                                    • Incremental reload

                                                                                                      Hi Neetu,

                                                                                                      Thanks for your reply. I was running the application but I was not able to find whether incremental load is working fine or not.

                                                                                                      What I noticed is it fetched 144rows when I reloaded the application. By question is why it is loading 144 rows again when we are doing the incremental load.

                                                                                                      Last row is the row which inserted in to the excel sheet. If the incremental load is working fine it should have loaded only one row.

                                                                                                      I have attached the application again please check it again and reply me.

                                                                                                       

                                                                                                        • Incremental reload
                                                                                                          Shumail Hussain

                                                                                                          because of some security checks i couldn't download ur file, can u pls send ur attachement to shumail.hussain@fgb.ae. try to send it in qvw format if possible or zip.

                                                                                                            • Incremental reload

                                                                                                              As requested I have sent you the document to your email. Hope to receive some solution from you very soon.

                                                                                                                • Incremental reload
                                                                                                                  Shumail Hussain

                                                                                                                  You have 5 attachement in the incremental.rar file... can you tell me which one is not working. plus as the area_mst.xls file updated or more data input and when u load the area_mst.xls file then automatically u get the incremental records. what is the issue?

                                                                                                                  Shumail

                                                                                                                    • Incremental reload

                                                                                                                      Hi Shumail,

                                                                                                                      I sent you the sample document to you to work on it. Requesting you to work on it and provide me some solution for incremental load.

                                                                                                                      By the way I am not able to understand how incremental load will happen automatically as said by you.

                                                                                                                       

                                                                                                                        • Incremental reload
                                                                                                                          Shumail Hussain

                                                                                                                          You need to schedule your Incremantal.qvw file on publisher to load it periodically.

                                                                                                                          If this is not you want then I think we are miscommunicating here, can u explain what do u mean by not working?

                                                                                                                          Shumail

                                                                                                                            • Incremental reload

                                                                                                                              Leave everything apart! I want to do the incremental load in the application attached by me.

                                                                                                                              I don't want to schedule or load it periodically. What I want is I want to load newly inserted,updated and deleted data incrementally instead of loading all the data every time. This is what meant as incremental load i suppose. Correct me if I am wrong!

                                                                                                                               

                                                                                                                                • Incremental reload
                                                                                                                                  Shumail Hussain

                                                                                                                                  Incremental load is useful where we have billions of records with large number of fields and we dont want to load the full data every time then we use qvd for incremental load, but here also we need to execute the script for incremental data.

                                                                                                                                  As far as my knowledge is concern automatic loading without execution can not be possible. You are looking for the alernate of publisher cababilities. If you want an automatic load, then you need to schedule it periodically which can only be happen by publisher.

                                                                                                                                  Shumail

                                                                                                                                    • Incremental reload

                                                                                                                                      Hi Shumail,

                                                                                                                                      I am currently looking for automatic reload only. That isn't possible with the QlikView Desktop? I was not knowing that.

                                                                                                                                      But anyway if it is happening by scheduling it then that is good enough for me so that I don't need to load all the data every time. What I want is to load the only the new(inserted/updated/deleted) data.

                                                                                                                                      So when with out scheduling when I click on the reload button it should load only the new data instead of loading all the data.

                                                                                                                                       

                                                                                                                                      • Incremental reload

                                                                                                                                        Hi Shumail,

                                                                                                                                        Can you please help me out to do the incremental load manually(whenever I reload the data). I need your help very badly!

                                                                                                                                         

                                                                                                                                          • Incremental reload
                                                                                                                                            Shumail Hussain

                                                                                                                                            dude please go up and refer my post on Tue, Jul 27 2010 11:20 AM, i hope that would help u. Sorry i couldn't reply u as i was stuck in some project.

                                                                                                                                            Shumail

                                                                                                                                              • Incremental reload

                                                                                                                                                Hi Shumail,

                                                                                                                                                Sorry for the trouble. I went up and saw the same with the above dates but didn't find any post of yours with same date and time. Kindly put the same post again now.

                                                                                                                                                 

                                                                                                                                                  • Incremental reload
                                                                                                                                                    Shumail Hussain

                                                                                                                                                    Rikab,

                                                                                                                                                    There are different ways of doing Incremental load, asaan tareeqa hai kai.... humain ek loop lagana hota hai magar iss main ek dynamic variable hota hai (for each File in filelist ('$(path)CHT*.dat')), check out some of my simple example:

                                                                                                                                                    if required tu.... pehlai ek configuration file hum bana saktai hain.... and define some variable here if you need it i.e. configuration.txt

                                                                                                                                                     


                                                                                                                                                    -------Configuration.txt-----------
                                                                                                                                                    let CurrentDay= day(now()-1) ;
                                                                                                                                                    let CurrentMonth= 06;
                                                                                                                                                    let CurrentYear= 2010 ;
                                                                                                                                                    let mn='06Jun10';
                                                                                                                                                    let prepath='C:\QlikView';

                                                                                                                                                     

                                                                                                                                                    next step is u need to create a qvw file for incremental load / generate incremental qvd.

                                                                                                                                                     


                                                                                                                                                    $(include=Configuration.txt);
                                                                                                                                                    let
                                                                                                                                                    path = '$(prepath)' & '\Input\' & '$(mn)' & '\Account\' ;
                                                                                                                                                    let Outputpath= '$(prepath)' & '\Output\' & '$(mn)' & '\Account\QVDs\' ;

                                                                                                                                                    for each File in filelist ('$(path)CHT*.dat')
                                                                                                                                                    let
                                                                                                                                                    CurrentFileName = right('$(File)', (len('$(File)') - len('$(path)')) ) ;
                                                                                                                                                    let LoadDate = mid('$(CurrentFileName)', 7,2) ;
                                                                                                                                                    load
                                                                                                                                                    '$(File)' as FileName,
                                                                                                                                                    FileTime( '$(File)' ) as FileTime,
                                                                                                                                                    FileSize( '$(File)' ) as Size
                                                                                                                                                    autogenerate (1);

                                                                                                                                                    Txn:
                                                                                                                                                    LOAD
                                                                                                                                                    @1:3 as TxnCode,
                                                                                                                                                    @4:22 as Acc_no,
                                                                                                                                                    @46:51 as Purchase_Date,
                                                                                                                                                    @52:63 as Destination_Amount,
                                                                                                                                                    @64:66 as Destination_Currency_Code,
                                                                                                                                                    @67:106 as Source_Amount,
                                                                                                                                                    $(LoadDate) as LoadDate
                                                                                                                                                    FROM [$(path)$(CurrentFileName)] (ansi, fix, no labels, header is 27, record is line);
                                                                                                                                                    Next File ;

                                                                                                                                                    store Txn into $(Outputpath)Txn_$(mn).qvd ;
                                                                                                                                                    //store CH$(CurrentFileName) into $(Outputpath)CH$(CurrentFileName).qvd ;

                                                                                                                                                    drop table Txn;


                                                                                                                                                    path variable use to take input file and outputpath is using for output qvd file. iss main hota ya hai kai daily basis pai transaction ki ek new file aa jati hai jisai hum TXN_mn.qvd main daal daitai hain simple. try the above code, i hope it would help u
                                                                                                                                                    Shumail
                                                                                                                                                      • Incremental reload

                                                                                                                                                        Hi Shumail,

                                                                                                                                                        I am not able to understand how it works so I my not be able to implement the same thing in my document also. If time permits please try to work in my document or else its ok I will try to find some other solution as I can't expect some one to work on my file if they are not free.

                                                                                                                                                        Many thanks for your help these days!

                                                                                                                                                         

                                                                                                                                                          • Incremental reload
                                                                                                                                                            Shumail Hussain

                                                                                                                                                            Hi Rikab,

                                                                                                                                                            I couldn't reply u since last 2 days was bit busy in month end number, yesterday i wrote a full scenario for u but i couldnt post it as well. In my last post I sent u some basics of my incremental load i think which was bit complex to understand. I think the help given on this is very easy to understand, please see the help and search

                                                                                                                                                            Using QVD files for incremental load

                                                                                                                                                            I hope this search will help u to understand the scenario of incremental load.Although regarding work on ur file, I think ur file is so simple and there is no need to develop incremental load process, u can either live with the publisher schedule only.

                                                                                                                                                            Shumail



                                                                                                                                                              • Incremental reload

                                                                                                                                                                Hi Shumail,

                                                                                                                                                                Thanks for your response! Yes it is very true that example given in the help is much easier when compared to the one posted by you.

                                                                                                                                                                It looks easy when I see that but I am not able to do it practically. So requesting you to apply the same logic as given in the help. No matters if it is very simple. I just wanted to learn how incremental load can be done. So can you please work on it so that I can apply the same logic for the one which is larger.

                                                                                                                                                                Please do reply!

                                                                                                                                                                  • Incremental reload
                                                                                                                                                                    Shumail Hussain

                                                                                                                                                                    I wish no one would get into this flat file crazy format, the development for ETL on this crazy data will take lots of time…
                                                                                                                                                                    Ok Dude NP I am telling you this step by step. Actually Incremental load is based on the requirement i.e. source and data which you are getting.

                                                                                                                                                                    We need to take care of three areas Input (Source TXT/ FLAT FILE), Process (Transformation, ETL, Database i.e. QVD) and Output (QVW).

                                                                                                                                                                    INPUT:

                                                                                                                                                                    First I would like to explain you about the source, the source data can be in any format and is not delimited but the files in daily folders has the equal format and incremental/appended. So the source where I am getting data is not a database structure it's actually a flat file which can be present in some network folder on daily basis. Eg: for July 2010 data I have following C Drive folders

                                                                                                                                                                    C:\Source\20100701
                                                                                                                                                                    C:\Source\20100702
                                                                                                                                                                    C:\Source\20100703
                                                                                                                                                                    C:\Source\20100704
                                                                                                                                                                    C:\Source\20100705
                                                                                                                                                                    C:\Source\20100706
                                                                                                                                                                    .
                                                                                                                                                                    .
                                                                                                                                                                    .
                                                                                                                                                                    .
                                                                                                                                                                    .
                                                                                                                                                                    C:\Source\20100730
                                                                                                                                                                    C:\Source\20100731


                                                                                                                                                                    Consider we have 2 files in each daily folder, one is incremental and other is appended, and take them as file A and B respectively.


                                                                                                                                                                    PROCESS:


                                                                                                                                                                    Create a new QVW file to generate QVD for flat file A and separately for flat file B. In each qvw file you need to develop the transformation mechanism for the file into structure / your required database format and store it into NEW QVD, make sure your coding should be that much flexible that on daily basis you can schedule it into publisher. So you have a separate code for incremental and appended data. Then when you schedule it in publisher this qvd will automatically be updated with the new data and other will be updated with the appended data.

                                                                                                                                                                    In my prior post I mention about the configuration file this is the file where you can define the source path, file format or you can define some batch file if the flat file in a zip format to extract.

                                                                                                                                                                    After development you need to schedule the qvw's file in publisher as per the availability of source file.

                                                                                                                                                                    OUTPUT:

                                                                                                                                                                    This is your final file where you can see and perform all the analysis on daily basis and will be scheduled in publisher as well. For output you need to develop and design a qvw file which is getting data from QVD file which you generated in process level.

                                                                                                                                                                     

                                                                                                                                                                    Shumail

                                                                                                                                                                      • Incremental reload

                                                                                                                                                                        Hi Shumail,

                                                                                                                                                                        Thank you so much for your effort! But sorry to tell you again that I am not able to understand and implement your ideas practically.

                                                                                                                                                                        Still I am thinking why you are not been able to work in my document. I feel that it will be easy for me to understand how incremental load works. Kindly think about working on in my document so that is easily understandable.

                                                                                                                                                                         

                                                                                                                                                                         

                                                                                                                                        • Incremental reload

                                                                                                                                          Hey Neetu,

                                                                                                                                          I am looking forward for your reply. Please reply me at the earliest when you are free. Hope I will get some solution soon.

                                                                                                                                           

                                                                                                                                          • Re: Incremental reload
                                                                                                                                            vikas mahajan

                                                                                                                                            Thanks for Sharing

                                                                                                                                             

                                                                                                                                            Vikas

                                                                                                                                            • Re: Incremental reload
                                                                                                                                              vikas mahajan

                                                                                                                                              Can you tell me please what  UPPER(incremental_build)  in your script.

                                                                                                                                               

                                                                                                                                              Vikas

                                                                                                                                • Incremental reload

                                                                                                                                  thanks man it really good.works fine

                                                                                                                                • Incremental reload
                                                                                                                                  Shumail Hussain

                                                                                                                                  Rikab,

                                                                                                                                  There are different ways of doing Incremental load, asaan tareeqa hai kai.... humain ek loop lagana hota hai magar iss main ek dynamic variable hota hai (for each File in filelist ('$(path)CHT*.dat')), check out some of my simple example:

                                                                                                                                  if required tu.... pehlai ek configuration file hum bana saktai hain.... and define some variable here if you need it i.e. configuration.txt

                                                                                                                                   


                                                                                                                                  -------Configuration.txt-----------
                                                                                                                                  let CurrentDay= day(now()-1) ;
                                                                                                                                  let CurrentMonth= 06;
                                                                                                                                  let CurrentYear= 2010 ;
                                                                                                                                  let mn='06Jun10';
                                                                                                                                  let prepath='C:\QlikView';


                                                                                                                                  next step is u need to create a qvw file for incremental load / generate incremental qvd.

                                                                                                                                   


                                                                                                                                  $(include=Configuration.txt);
                                                                                                                                  let
                                                                                                                                  path = '$(prepath)' & '\Input\' & '$(mn)' & '\Account\' ;
                                                                                                                                  let Outputpath= '$(prepath)' & '\Output\' & '$(mn)' & '\Account\QVDs\' ;

                                                                                                                                  for each File in filelist ('$(path)CHT*.dat')
                                                                                                                                  let
                                                                                                                                  CurrentFileName = right('$(File)', (len('$(File)') - len('$(path)')) ) ;
                                                                                                                                  let LoadDate = mid('$(CurrentFileName)', 7,2) ;
                                                                                                                                  load
                                                                                                                                  '$(File)' as FileName,
                                                                                                                                  FileTime( '$(File)' ) as FileTime,
                                                                                                                                  FileSize( '$(File)' ) as Size
                                                                                                                                  autogenerate (1);

                                                                                                                                  Txn:
                                                                                                                                  LOAD
                                                                                                                                  @1:3 as TxnCode,
                                                                                                                                  @4:22 as Acc_no,
                                                                                                                                  @46:51 as Purchase_Date,
                                                                                                                                  @52:63 as Destination_Amount,
                                                                                                                                  @64:66 as Destination_Currency_Code,
                                                                                                                                  @67:106 as Source_Amount,
                                                                                                                                  $(LoadDate) as LoadDate
                                                                                                                                  FROM [$(path)$(CurrentFileName)] (ansi, fix, no labels, header is 27, record is line);
                                                                                                                                  Next File ;

                                                                                                                                  store Txn into $(Outputpath)Txn_$(mn).qvd ;
                                                                                                                                  //store CH$(CurrentFileName) into $(Outputpath)CH$(CurrentFileName).qvd ;

                                                                                                                                  drop table Txn;


                                                                                                                                  path variable use to take input file and outputpath is using for output qvd file. iss main hota ya hai kai daily basis pai transaction ki ek new file aa jati hai jisai hum TXN_mn.qvd main daal daitai hain simple. try the above code, i hope it would help u

                                                                                                                                  Shumail



                                                                                                                              • Incremental reload

                                                                                                                                Hi Rob,

                                                                                                                                I referred your cookbook for doing the incremental load. It is really an wonderful work and it will be helpful for everyone. After that going through the script of the incremental load found that it does not handle the deletes.

                                                                                                                                I wanted to perform delete also! Do you have any other incremental load document which can handles delete also(Including insert and update)? If not can you please create the same and update the cookbook. It will be very useful for all those who wants to handle deletes in incremental load.

                                                                                                                                Let me know one it is updated!

                                                                                                                                 

                                                                                                                                  • Incremental reload

                                                                                                                                    Hello Attitude,

                                                                                                                                    I have a similar requirement like mentioned by you here. I have 6 QVDs from a Oracle DB, I was wondering when I do the incremental load will it update the daily changes only or reload the whole QVD and then update?? Can you please help me with the same issue I am struck with like you had sometime back.

                                                                                                                                    Thanks in Advance,

                                                                                                                                    ANDY

                                                                                                                                      • Incremental reload
                                                                                                                                        s j

                                                                                                                                        The most difficult case to handle is when records are actually deleted from the source database between script executions. The following conditions apply:

                                                                                                                                        · The data source can be any database.

                                                                                                                                        · QlikView loads records inserted into the database or updated in the database after the last script execution.

                                                                                                                                        · QlikView removes records deleted from the database after the last script execution.

                                                                                                                                        · A field ModificationDate (or similar) is required for QlikView to recognize which records are new.

                                                                                                                                        · A primary key field is required for QlikView to sort out updated records from the QVD file.

                                                                                                                                        · This solution will force the reading of the QVD file to "standard mode" (rather than "super-fast mode"), which is still considerably faster than loading the entire database.

                                                                                                                                         

                                                                                                                                        Script example:

                                                                                                                                        Let ThisExecTime = Now( );

                                                                                                                                         

                                                                                                                                        QV_Table:

                                                                                                                                        SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

                                                                                                                                        WHERE ModificationTime >= #$(LastExecTime)#

                                                                                                                                        AND ModificationTime < #$(ThisExecTime)#;

                                                                                                                                         

                                                                                                                                        Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

                                                                                                                                        WHERE NOT EXISTS(PrimaryKey);

                                                                                                                                         

                                                                                                                                        Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

                                                                                                                                         

                                                                                                                                        If ScriptErrorCount = 0 then

                                                                                                                                        STORE QV_Table INTO File.QVD;

                                                                                                                                        Let LastExecTime = ThisExecTime;

                                                                                                                                        End If

                                                                                                                                         

                                                                                                                                          • Incremental reload

                                                                                                                                            Hello Sunil,

                                                                                                                                            Thank you for the quick reply, but the problem is I have a Primary field called TIME_PERIOD_ID from where I have to get the 2 variables? the format of this from Oracle DB is somethinglike below, where 2011 is year, 03 is month, 1 is normal week and 13 is Week number. So I want to try Incremental load today and tommorow and check if its working as expected. According to what I think I will get all the sales data for this date and tommorow or next week, when I rerun the script with variables, then it should look for Week 14 data only and load and not the whole data right? Also how to use my Time period Id for the below script??

                                                                                                                                            WHERE

                                                                                                                                             



                                                                                                                                            ModificationTime >= #$(LastExecTime)#

                                                                                                                                            AND

                                                                                                                                             



                                                                                                                                            ModificationTime < #$(ThisExecTime)#;

                                                                                                                                             

                                                                                                                                             



                                                                                                                                            ,201103113' ,

                                                                                                                                            Thanks a TON,

                                                                                                                        • Incremental reload

                                                                                                                          I am having the same issue the On incremental load there has no primary key being there in table due to which there is being a problem of doing incremental load. Please help in this matter..

                                                                                                                          • Re: Incremental reload
                                                                                                                            Srikanth P

                                                                                                                            Hi , If you want Implement the Incremental loading the table must have below characteristics depends on your Incremental load type:

                                                                                                                             

                                                                                                                            Only INSERT or NEW --> DATE FIELD (This must be tells us weather row is new or not w.r,t previous load)

                                                                                                                             

                                                                                                                            INSERT & UPDATE --> DATE FIELD & PRIMARY KEY

                                                                                                                             

                                                                                                                            INSERT, UPDATE & DELETE --> DATE FIELD & PRIMARY KEY