topic Re: Filling a sequence - using 1st and last entry in QlikView

Filling a sequence - using 1st and last entry

Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlikheads,

I am currently trying to recon 3 years worth of sales. I have already imported all sales by serial number into a qlikview document, but now I need to see where each serial number was shipped to. The shipments file I have received has the first and last serial number of each shipment, but not the middle values.

The data is in an excel file and looks like this:

TSTAMP	DESTINATION	SERIALNO	LASTINRANGE
Sep/13	9372	910002071588	910002271587
Sep/13	7629	920001711543	920001711562
Oct/13	5591	960000062807	960000072806
Oct/13	7890	900000967163	900001087162

What i need to do is match the serial number to the correct shipment so that I can return the destination for each serial number.

Any help will be greatly appreciated.

Re: Filling a sequence - using 1st and last entry

miguelbraga — Mon, 30 May 2016 16:49:56 GMT

Hey there,

Can you please put a sample of your current data and your desired data so that we can provide a solution? With your given input in the question I can't develop a solution for you. Can you please explain better your issue with screenshots, photos form designs in paper, word documents, etc...

Regards,

Re: Filling a sequence - using 1st and last entry

Kushal_Chawda — Mon, 30 May 2016 17:01:16 GMT

You can do something like below

Data:

LOAD

TSTAMP,

DESTINATION,

SERIALNO,

LASTINRANGE,

SERIALNO+iterno()-1 as FILLED_SERIAL

FROM table

while SERIALNO+iterno()-1 <= LASTINRANGE;

Note : There will be a performance impact if the Data is huge.

Re: Filling a sequence - using 1st and last entry

ziadm — Mon, 30 May 2016 17:24:25 GMT

If you do not have a pattern in creating the serial no you will not be able to create the numbers. if you do have a pattern having the first and last digit please explain how this pattern can be developed

Thanks