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/
If you want to split your records, you can do it like this:
Set DateFormat = 'DD/MM/YYYY';
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 [
] (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.