Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Adding new .xlsx to current data in Qlik Sense

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?

8 Replies
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.


talk is cheap, supply exceeds demand
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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.

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

And should new data be added to all tables from the auto-generated section? Or is there only new data for some of the tables?


talk is cheap, supply exceeds demand
Not applicable
Author

It is required for all the tables.

Gysbert_Wassenaar

Ok after the initial reload add the word ADD in front of LOAD for each load block. You'll have to unlock the auto-generated section first before you can make changes to it. Then execute a partial reload. See here for how to do that: Qlik Sense Partial Reload for v2.0.x, v2.1.1, v3.x


talk is cheap, supply exceeds demand