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: 
Anonymous
Not applicable

Insert a record based on load data

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.

      

CustomerCheckInDateCheckOutDateSpanSalesPricePricePerNightRes. no.
Agency 0128/01/201802/02/201811,570.00 3146555
Agency 0110/03/201819/03/201803,204.00 3567091
Agency 0224/01/201831/01/201801,540.00 2206267
Agency 0223/02/201802/03/201811,582.70 226.16621
Agency 0206/03/201813/03/201801,582.70 226.17241
Agency 0222/03/201829/03/201801,666.00 2386991
Agency 0227/03/201803/04/201811,582.70 226.16701
Agency 0227/03/201803/04/201812,198.00 3147097
Agency 0204/04/201811/04/201801,932.70 276.16159

On this table, the first record must split in two as follows:

       

CustomerCheckInDateCheckOutDateSpanSalesPricePricePerNightRes. no.
Agency 0128/01/201831/02/201811,570.00 3146555
Agency 0101/02/201802/02/201811,570.00 3146555

Any clue as to how can I do this?

Regards,

Jorge Aragon

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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');

View solution in original post

6 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

http://masterssummit.com

http://qlikviewcookbook.com

swuehl
MVP
MVP

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');

Anonymous
Not applicable
Author

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

Anonymous
Not applicable
Author

Thank you Rob!!

Anonymous
Not applicable
Author

Thank you Peter, it worked great.