9 Replies Latest reply: Oct 11, 2016 2:34 PM by Fikri Shihab RSS

    Concatenate QVD file with resident load

    Fikri Shihab

      Hi Qlikview experts,

       

      I have a requirement where I need to append new data to the existing QVD file(s).  Initial run when creating the QVD file also includes resident load for certain calculation.

       

      When appending with new raw data I will also need to perform resident load and append the latest data to the existing QVD file and save it to latest QVD file.

       

      While I was able to run the script the new data does not include any new result with resident load.

       

      What I did was

       

      Table_Name:

      LOAD *

      FROM

      Filename.qvd (qvd);

       

      concatenate

      LOAD distinct

      FROM

      Filename.csv

       

      left join

      LOAD

      Field Names

      Resident Table Name

      where xxxx (condition)

      ORDER by filed name;

       

      store Table_Name into new_table_name.qvd(qvd);

       

      Any input or suggestion?

          • Re: Concatenate QVD file with resident load
            Fikri Shihab

            Hi Sunny,

             

            Thanks, I was actually able to append the new records. The only problem

            that I have is the result of the calculation in the resident load is not

            appended to the QVD, but all new records are appended.

             

            How do I get the new calculated  values with the new records are also

            appended?

              • Re: Concatenate QVD file with resident load
                Stefan Wühl

                There are multiple possible reasons why your code does not work as intended.

                 

                Maybe the JOIN keys don't show matching values, or your WHERE clause filters out all records.

                 

                Hard to tell looking at your pseudo code.

                  • Re: Concatenate QVD file with resident load
                    Fikri Shihab

                    Actually I just simply copied the resident load part from the initial Load

                    and where clause filter is also the same with this initial run.

                    I can the new records are appended, but not the new values in the resident

                    load part.

                     

                    Can the calculation with new records be appended in Qlikview?

                     

                     

                     

                    On Wed, Oct 5, 2016 at 5:19 PM, Stefan Wühl <qcwebmaster@qlikview.com>

                      • Re: Concatenate QVD file with resident load
                        Stefan Wühl

                        Seems I am missing what you are trying to achieve with the LEFT JOIN of your resident LOAD (if it's just a copy of the initial load and where clause is the same, what's the goal here?).

                         

                        Would be good to see a more realistic script snippet and some sample lines of data.

                          • Re: Concatenate QVD file with resident load
                            Fikri Shihab

                            Hi,

                             

                            Perhaps my description was not very clear. Here is more detail information. Hope it helps

                             

                            I have data in csv files that consists of date, time and other fields, let say C, D, E, F. The file name keep adding on daily basis.

                            In the initial run when creating QVD file for the first month I did this:


                            Set vSourceData= 'Source file directory';

                            Set vQVDSave ='QVD Save directory';

                             

                            Status:

                             

                            Load Distinct

                              RecNo() as ID,

                              rep_date,

                              Date (rep_date_date,'DD-MMM-YYYY') as Date,

                              Month(rep_date) as Month,

                                 rep_time,

                                 area_id as Area,

                                 customer as [Customer Name],

                                 device_id as Device,

                                 Timestamp(Floor( Timestamp(rep_date & ' ' & rep_time,'DD/MM/YYYY hh:mm:ss'),1/288)) as [Time Stamp],

                             

                            FROM

                             

                            'Source Directory\*.csv'

                            (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

                             

                            where criteria;

                             

                            left join

                            switch:

                                 LOAD

                                   [Time Stamp], Date,

                                   [Area], [Customer Name],

                                  if([Customer Name] = peek('Customer Name') and [Area] <> peek('Area'),1,0) as switch

                                 Resident Status 

                                 Where wildmatch(criteria)

                                 ORDER BY [Customer Name],[Time Stamp];

                                  

                            Last_Record:

                             

                             

                            LOAD Max ([Time Stamp]) as Latest_Time_Stamp

                            Resident Status;

                             

                             

                            LET Last_Record= Peek(('Latest_Time_Stamp'), 0, 'Last_Record');

                            trace >>> $(Last_Record); 

                             

                            Table2:

                             

                            LOAD

                             

                            [Device],

                            Commercial

                                

                            FROM

                            'data source.xls'

                            (biff, embedded labels, table is Sheet1$);

                             

                            Table 3:

                             

                            LOAD

                            @2 as Unit, @3 as Sector , @15 as [Id],

                            mid(@12,10,7)  as [Device id]

                             

                             

                            FROM

                            'Source.csv'

                            (txt, codepage is 1252, no labels, delimiter is ',', msq);

                             

                            store Status into $(vQVDSave)Initial_status. qvd(qvd);

                             

                            This runs well and I got all the records I need in QVD file.

                             

                            In the the next run, what I did was like this:

                             

                            Status:

                            LOAD

                             

                            All fields in QVD file

                             

                            FROM QVD file.qvd (qvd);

                             

                            concatenate

                             

                            Load Distinct

                              RecNo() as ID,

                              rep_date,

                              Date (rep_date_date,'DD-MMM-YYYY') as Date,

                              Month(rep_date) as Month,

                                 rep_time,

                                 area_id as Area,

                                 customer as [Customer Name],

                                 device_id as Device,

                                 Timestamp(Floor( Timestamp(rep_date & ' ' & rep_time,'DD/MM/YYYY hh:mm:ss'),1/288)) as [Time Stamp],

                             

                            FROM

                             

                            'Source Directory\*.csv'

                            (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

                             

                            where [Time Stamp] > >= '$(Last_Record)'

                             

                            left join

                            //switch:

                                 LOAD

                                   [Time Stamp], Date,

                                   [Area], [Customer Name],

                                  if([Customer Name] = peek('Customer Name') and [Area] <> peek('Area'),1,0) as switch

                                 Resident Status 

                                 Where wildmatch(criteria)

                                 ORDER BY [Customer Name],[Time Stamp];

                                  

                            Last_Record:

                             

                             

                            LOAD Max ([Time Stamp]) as Latest_Time_Stamp

                            Resident Status;

                             

                             

                            LET Last_Record= Peek(('Latest_Time_Stamp'), 0, 'Last_Record');

                            trace >>> $(Last_Record); 

                             

                            Table2:

                             

                            LOAD

                             

                            [Device],

                            Commercial

                                

                            FROM

                            'data source.xls'

                            (biff, embedded labels, table is Sheet1$);

                             

                            Table 3:

                             

                            LOAD

                            @2 as Unit, @3 as Sector , @15 as [Id],

                            mid(@12,10,7)  as [Device id]

                             

                             

                            FROM

                            'Source.csv'

                            (txt, codepage is 1252, no labels, delimiter is ',', msq);

                             

                            store Status into $(vQVDSave)Updated_status. qvd(qvd);


                            The next run I just use the latest qvd file to append with the new records. New records are appended but  not this one.


                            switch:

                                 LOAD

                                   [Time Stamp], Date,

                                   [Area], [Customer Name],

                                  if([Customer Name] = peek('Customer Name') and [Area] <> peek('Area'),1,0) as switch

                                 Resident Status 

                                 Where wildmatch(criteria)

                                 ORDER BY [Customer Name],[Time Stamp];

                             

                            I hope this is clear enough.

                             

                            Thanks for help

                              • Re: Concatenate QVD file with resident load
                                Stefan Wühl

                                [switch] field is already part of table Status, isn't it?

                                 

                                If yes, it's considered a key field and I don't think that's what you want here.

                                 

                                Besides that, what is your criteria you are using in the WHERE clause?

                                  • Re: Concatenate QVD file with resident load
                                    Fikri Shihab

                                    Yes, [Switch] field is already part of the table status in QVD file and that is a result of the calculation in the previous run, not in the new records. 


                                    Actually, I did  not make a separate table for that switch. It was made as comment //switch before join statement.


                                    This field [switch] is not the csv file, so  I am assuming I have to do another calculation every time I downloaded new records  and put the results to the same field [switch] and then append them to the QVD file that has values from the previous records and calculation.  All other fields seems appended but not this [switch] field.


                                    The criteria that I used in the second run (concatenating QVD with new csv files) is the same as when QVD was initially created. I copied that criteria from initial run.

                                    It is something like this:


                                    Where wildmatch(device, '*CX*'), 'IN', 'OUT')

                                     

                                    Anything missing from my code?

                                    • Re: Concatenate QVD file with resident load
                                      Fikri Shihab

                                      I was finally able to get it work with this approach:

                                       

                                      Table1:

                                      LOAD

                                       

                                      FROM

                                      QVD file.qvd (qvd);

                                       

                                      Last Record:

                                      LOAD Max ([Time Stamp]) as Latest_Time_Stamp

                                      Resident Table1:;

                                        LET Last_Record= Peek(('Latest_Time_Stamp'), 0, 'Last_Record');

                                      trace >>> $(Last_Record);

                                       

                                      Drop Table 1;

                                       

                                      Updated_Status:

                                      LOAD

                                      [Fields in csv file]

                                      FROM

                                      csvfile.csv

                                      (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

                                      where [Time Stamp] > '('Last_Record')'

                                       

                                      Left join

                                      LOAD

                                      [Some fields]

                                      calculation statement

                                      Resident Updated_Status

                                      where xxx

                                      order by

                                      // the above is also part of calculation in the initial load - Please see previous posts

                                       

                                      concatenate

                                      [Fields in QVD file]

                                      //same fields with csv file

                                      FROM

                                      QVD file.qvd (qvd);


                                      LOAD Table 2

                                      FROM

                                      csvfile.csv

                                      (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

                                      // Note this join and the result was also part of the initial load when creating initial qvd file


                                      Store Updated Status into Updated_status.qvd(qvd);


                                      I also want to get the latest time stamp from the latest updated file so that I can show this field also. In this case it seems I need to add another statement before storing the file to latest qvd file. Something like this:


                                      Last Record:

                                      LOAD Max ([Time Stamp]) as Latest_Time_Stamp

                                      Resident Updated_Status

                                        LET Last_Record= Peek(('Latest_Time_Stamp'), 0, 'Last_Record');

                                      trace >>> $(Last_Record);

                                       

                                      The question that I have is why do I have to drop the table first and reload them again to make it work?  Since I have over 12 million records to concatenate, it makes much longer time to complete this process and I feel it is inefficient.


                                      Could someone explain to me about this and is there a better way to efficiently do this?