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
