topic Insert a record based on load data in QlikView

Insert a record based on load data

Anonymous — Wed, 14 Feb 2018 22:37:49 GMT

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

Re: Insert a record based on load data

rwunderlich — Wed, 14 Feb 2018 22:51:28 GMT

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

Re: Insert a record based on load data

swuehl — Wed, 14 Feb 2018 22:54:59 GMT

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

Re: Insert a record based on load data

Anonymous — Fri, 16 Feb 2018 01:05:34 GMT

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

Peter_Cammaert — Fri, 16 Feb 2018 09:21:35 GMT

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.

Re: Insert a record based on load data

Anonymous — Fri, 16 Feb 2018 18:06:16 GMT

Thank you Rob!!

Re: Insert a record based on load data

Anonymous — Fri, 16 Feb 2018 18:06:41 GMT

Thank you Peter, it worked great.