Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have a Reservations table in Excel format and when loading to QV need to create additional records when a reservation crosses month-end, so a record exists for month 1 and another for month 2.
Customer | CheckInDate | CheckOutDate | Span | SalesPrice | PricePerNight | Res. no. |
Agency 01 | 28/01/2018 | 02/02/2018 | 1 | 1,570.00 | 314 | 6555 |
Agency 01 | 10/03/2018 | 19/03/2018 | 0 | 3,204.00 | 356 | 7091 |
Agency 02 | 24/01/2018 | 31/01/2018 | 0 | 1,540.00 | 220 | 6267 |
Agency 02 | 23/02/2018 | 02/03/2018 | 1 | 1,582.70 | 226.1 | 6621 |
Agency 02 | 06/03/2018 | 13/03/2018 | 0 | 1,582.70 | 226.1 | 7241 |
Agency 02 | 22/03/2018 | 29/03/2018 | 0 | 1,666.00 | 238 | 6991 |
Agency 02 | 27/03/2018 | 03/04/2018 | 1 | 1,582.70 | 226.1 | 6701 |
Agency 02 | 27/03/2018 | 03/04/2018 | 1 | 2,198.00 | 314 | 7097 |
Agency 02 | 04/04/2018 | 11/04/2018 | 0 | 1,932.70 | 276.1 | 6159 |
On this table, the first record must split in two as follows:
Customer | CheckInDate | CheckOutDate | Span | SalesPrice | PricePerNight | Res. no. |
Agency 01 | 28/01/2018 | 31/02/2018 | 1 | 1,570.00 | 314 | 6555 |
Agency 01 | 01/02/2018 | 02/02/2018 | 1 | 1,570.00 | 314 | 6555 |
Any clue as to how can I do this?
Regards,
Jorge Aragon
If you want to split your records, you can do it like this:
Set DateFormat = 'DD/MM/YYYY';
CheckOuts:
LOAD *,
If(IterNo() = 1, CheckInDate, DayName(Monthstart(CheckInDate,iterno()-1))) as CheckIn2,
If( Monthstart(CheckInDate,iterno()-1)=Monthstart(CheckOutDate), CheckOutDate, DayName(Monthend(CheckInDate,iterno()-1))) as CheckOut2
While Monthstart(CheckInDate,iterno()-1) <= Monthstart(CheckOutDate);
LOAD *, recno() as RecID INLINE [
CheckInDate CheckOutDate
28/01/2018 02/02/2018
27/03/2018 03/04/2018
04/04/2018 11/04/2018
10/12/2017 03/02/2018
] (delimiter is '\t');
Rather than splitting the record, you might consider generating a link for the record to each date, using either IntervalMatch or a Previous() function. This will give you the flexibility to associate the transaction to any time period. Here are a couple of samples demonstrating the technique.
Qlikview Cookbook: Count Days In A Transaction Using Intervalmatch http://qlikviewcookbook.com/recipes/download-info/count-days-in-a-transaction-using-intervalmatch/
Qlikview Cookbook: Expand A Pricing Date Table http://qlikviewcookbook.com/recipes/download-info/expand-a-pricing-date-table/
-Rob
If you want to split your records, you can do it like this:
Set DateFormat = 'DD/MM/YYYY';
CheckOuts:
LOAD *,
If(IterNo() = 1, CheckInDate, DayName(Monthstart(CheckInDate,iterno()-1))) as CheckIn2,
If( Monthstart(CheckInDate,iterno()-1)=Monthstart(CheckOutDate), CheckOutDate, DayName(Monthend(CheckInDate,iterno()-1))) as CheckOut2
While Monthstart(CheckInDate,iterno()-1) <= Monthstart(CheckOutDate);
LOAD *, recno() as RecID INLINE [
CheckInDate CheckOutDate
28/01/2018 02/02/2018
27/03/2018 03/04/2018
04/04/2018 11/04/2018
10/12/2017 03/02/2018
] (delimiter is '\t');
I am trying top understand the INLINE command; on your answer you include speciic values, but I need the values from the record that its been read, there are thousands of reservations and once the model is working will be fed daily with new reservations.
How can I use the << LOAD *, recno() as RecID INLINE [ >> with the fields values?
Thank you for taking a look at this.
You need to adapt Stefan's working solution to your own environment. Since you didn't provide any details of the Excel you are trying to process, an INLINE example data set is usually very instructive to show you how it works.
The column names are already identical to your Excel column names (if your example data set is representative) Just replace the INLINE statement and everything that follows in Stefan's exmaple by something like:
FROM [YourExcelFilePath] (yourexcelfileoptions);
and try again. Replace the strings in italics whith the proper values for your Excel source.
Thank you Rob!!
Thank you Peter, it worked great.