8 Replies Latest reply: Dec 22, 2016 12:22 PM by Gysbert Wassenaar RSS

    Adding new .xlsx to current data in Qlik Sense

    Aviral Gupta

      Hi All,

      I have loaded a huge amount of data from two files: file1, file2 in Qlik Sense and created a data model.

      I want to add new data to this daily from file3, file4 in another folder on my desktop. I don't want to load file1, file2 again as it takes a lot of time.

      Can anyone please guide me?

        • Re: Adding new .xlsx to current data in Qlik Sense
          Gysbert Wassenaar

          You can try using a partial load: Add ‒ Qlik Sense

          But perhaps a better idea is to load the data each day and then store the resulting table into a qvd. The next day you load the data from the qvd (very fast) and append the data from the new day. And again store the resulting table to the qvd again. This is called incremental loading.

            • Re: Adding new .xlsx to current data in Qlik Sense
              Aviral Gupta

              Hi Gysbert,

              I read articles on incremental data load, but being new to Qlik Sense am finding it difficult to implement it in my script. Can you help me with the script if I share my current script with you along with the folder location?

              Thanks in advance.

                • Re: Adding new .xlsx to current data in Qlik Sense
                  Gysbert Wassenaar

                  Probably. Post your script and we'll have a look.

                    • Re: Adding new .xlsx to current data in Qlik Sense
                      Aviral Gupta

                      Thanks a lot. That would be a great help.

                      The current data is already loaded using the script below via a connection to a folder. The new data is present in another folder.

                      The script is in three sections:

                      • Main
                      • Auto-Generated Section
                      • Section-2

                       

                      //Main

                      SET ThousandSep=',';

                      SET DecimalSep='.';

                      SET MoneyThousandSep=',';

                      SET MoneyDecimalSep='.';

                      SET MoneyFormat='$#,##0.00;($#,##0.00)';

                      SET TimeFormat='h:mm:ss TT';

                      SET DateFormat='M/D/YYYY';

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

                      SET FirstWeekDay=6;

                      SET BrokenWeeks=1;

                      SET ReferenceDay=0;

                      SET FirstMonthOfYear=1;

                      SET CollationLocale='en-US';

                      SET CreateSearchIndexOnReload=1;

                      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

                      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                       

                      //Auto-Generated Section

                      [Booking]:

                      LOAD

                        [b'"Hotel Name".2] AS [Hotel Name],

                        [Hotel Code],

                        Date(Date#([Booked Date], 'YYYY-MM-DD') ) AS [Booked Date],

                          Timestamp([Arrival Date],'YYYY') AS [Test YearB],

                          [Booked Time],

                        //Date(Date#([Arrival Date], 'YYYY-MM-DD') ) AS [Arrival Date],

                          [Arrival Date],

                        Date(Date#([Departure Date], 'YYYY-MM-DD') ) AS [Departure Date],

                        [Arrival Time],

                        [Departure Time],

                        [Stay Duration],

                        [Res. Type],

                        [Pax (Adult+Child)],

                        [Room Type],

                        [Rate Type],

                        [Travel Agent],

                        [Company],

                        [Business source],

                        [Transaction Status],

                        [Res No],

                        [TA Voucher No],

                        [Promo Code],

                        [Folio No],

                          [Room Charges (Tax Excl.)],

                        [Room Charges Service Tax],

                        [Room Charges Luxury Tax],

                        [Room Charges Discount],

                        [Room Charges Adjustment],

                        [Extra Charges  (Tax Excl.)],

                        [Extra Charge Service Tax],

                        [Extra Charge Luxury Tax],

                        [Extra Charge Discount],

                        [Extra Charge Adjustment],

                        [Folio Level Discount],

                        [Folio Level Adjustment],

                        [Folio Level Balance Transfer],

                        [Total Advance Deposit],

                        [Total Due],

                        [Total Payment],

                        [Pay Currency],

                        [Void Date],

                        [Void Time],

                        [Void By],

                        [Void Reason],

                        Date(Date#([No Show Date], 'YYYY-MM-DD') ) AS [No Show Date],

                        [No Show Time],

                        [No Show By],

                        [No Show Reason],

                        Date(Date#([Cancelled Date], 'YYYY-MM-DD') ) AS [Cancelled Date],

                        [Cancelled Time],

                        [Cancelled By],

                        [Cancelled Reason],

                        [Customer Key],

                        [Guest Name],

                        [Email],

                        [Gender],

                        [Address],

                        [City],

                        [Zip],

                        [Country],

                        [Mobile No],

                        [Phone No],

                        [ID Type],

                        [ID Number],

                          ([b'"Hotel Name".2] & '-' & [Folio No] & '-' & Timestamp([Arrival Date],'YYYY')) As LinkID

                      FROM [lib://New folder/Booking.xlsx]

                      (ooxml, embedded labels, table is Booking);

                       

                       

                      [Posting]:

                      LOAD [Hotel Name],

                        [Hotel Code],

                        [Folio No] as [Folio No],

                          //Date(Date#([Date], 'YYYY-MM-DD') ) AS [Date],

                          [Date],

                          Timestamp([Date],'YYYY') AS [Test YearP],

                        [VoucherNo/ReceiptNo],

                        [Type],

                        [Particular],

                        [Qty],

                        [Currency],

                        [Amount],

                        [Service Tax],

                        [Luxury Tax],

                        [Discount],

                        [Adjustment],

                        [Total],

                        [Is Advance Deposit],

                        [Is Inclusion],

                        [Posted By],

                          ([Hotel Name] & '-' & [Folio No] & '-' & Timestamp([Date],'YYYY')) As LinkID

                      FROM [lib://New folder/Posting.xlsx]

                      (ooxml, embedded labels, table is Posting);

                       

                       

                      [Hostel]:

                      LOAD

                        [Hotel Name] AS [Hotel Name-HotelName],

                        [Hotel Code] AS [Hostel.Hotel Code],

                        [Room Types],

                        [Total Inventory],

                        [Available Inventory],

                        [Date],

                        [Blocked Rooms]

                      FROM [lib://New folder/Hostel.xlsx]

                      (ooxml, embedded labels, table is Hostel);

                       

                       

                      [Expense]:

                      LOAD

                        [HotelName] AS [Hotel Name-HotelName],

                        [HotelCode],

                        [VoucherDate],

                        [VoucherNo],

                        [ContactInfo],

                        [ExpenseMadeTo],

                        [PaidOut],

                        [PaidOutCharges],

                        [PaidOutCurrency],

                        [PaymentMode],

                        [PaymentCharges],

                        [PaymentCurrency]

                      FROM [lib://New folder/Expense.xlsx]

                      (ooxml, embedded labels, table is Expense);

                       

                       

                       

                       

                       

                       

                      [autoCalendar]:

                        DECLARE FIELD DEFINITION Tagged ('$date')

                      FIELDS

                        Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

                        Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

                        Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

                        Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

                        Month($1) AS [Month] Tagged ('$month', '$cyclic'),

                        Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

                        Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

                        Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

                        Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

                        Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified');

                       

                       

                      DERIVE FIELDS FROM FIELDS [Booked Date], [Arrival Date], [Departure Date], [No Show Date], [Cancelled Date], [Date] USING [autoCalendar] ;

                       

                      //Section-2

                      Key:

                         Load Distinct

                        LinkID,

                        [Hotel Name],

                        [Folio No],

                          Timestamp([Arrival Date],'YYYY') AS [Test YearB]

                         Resident Booking;

                       

                         Concatenate(Key)

                         Load Distinct

                        LinkID,

                        [Hotel Name],

                        [Folio No],

                          Timestamp([Date],'YYYY')AS [Test YearP]

                         Resident Posting;

                       

                         Drop Fields [Hotel Name],[Folio No],[Test YearB] From Booking;

                         Drop Fields [Hotel Name],[Folio No],[Test YearP] From Posting

                • Re: Adding new .xlsx to current data in Qlik Sense
                  Jonathan Dienst

                  Search this site for "incremental load" and "partial reload"