Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jaragono
New Contributor

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Insert a record based on load data

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

6 Replies

Re: Insert a record based on load data

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

MVP
MVP

Re: Insert a record based on load data

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

jaragono
New Contributor

Re: Insert a record based on load data

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.

Re: Insert a record based on load data

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.

jaragono
New Contributor

Re: Insert a record based on load data

Thank you Rob!!

jaragono
New Contributor

Re: Insert a record based on load data

Thank you Peter, it worked great.

Community Browser