topic Random Cycle Inventory in QlikView

Random Cycle Inventory

Wed, 03 Feb 2016 21:27:31 GMT

Hi All,

I had a question ask about Random Cycle Inventory Report. I have attached a spreadsheet of item numbers. What the client was wondering is if there was a way to design a report in Qlikview that would go in and randomly pick 25-50 items so they can do spot checks on their inventory. Has anyone ever done anything like this.

Thanks,

David

Re: Random Cycle Inventory

tamilarasu — Thu, 04 Feb 2016 02:21:46 GMT

Hi David,

Try,

SAMPLE 0.01 LOAD ItemNumber
FROM
[Inventory Items.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Random Cycle Inventory

sunny_talwar — Thu, 04 Feb 2016 02:37:17 GMT

Never heard about the SAMPLE function before. Thanks for introducing to a new function Tamil

Best,

Sunny

Re: Random Cycle Inventory

petter — Thu, 04 Feb 2016 02:46:13 GMT

You can also create a sample dynamically in your app having an input of the % sample you want interactively:

Re: Random Cycle Inventory

jagan — Thu, 04 Feb 2016 02:56:06 GMT

Hi,

Just place Sample % in front of the Load statement

Example: Sample 0.05 // Pulls 5% of random records from the data set.

OR manual process

Data:

LOAD

ItemNumber,

RecNo() AS Sno

FROM

[Inventory Items.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE MixMatch(RecNo(), Floor(Rand() * 2858), Floor(Rand() * 2858), Floor(Rand() * 2858), Floor(Rand() * 2858), Floor(Rand() * 2858), Floor(Rand() * 2858));

Note: 2858 is number of records in your excel file. I just filtered 6 records if you need further items just add

Floor(Rand() * 2858) as many times as you required in the Match().

Hope this helps you.

Regards,

Jagan.

Re: Random Cycle Inventory

tamilarasu — Thu, 04 Feb 2016 03:10:02 GMT

Below one takes the percentage dynamically irrespective of records count.

//Set your max and min range
Set vMaxLimit = 50;
Set vMinLimit = 25;

Temp:
LOAD ItemNumber
FROM
[Inventory Items.xlsx]
(ooxml, embedded labels, table is Sheet1);

Let vRandom = (Rand()*(vMaxLimit-vMinLimit)+ vMinLimit) / NoOfRows('Temp');  

NoConcatenate
SAMPLE $(vRandom) LOAD ItemNumber
Resident Temp;

DROP Table Temp;

Re: Random Cycle Inventory

Thu, 04 Feb 2016 13:23:39 GMT

Hi Tamil,

Thank you so much it worked like a champ. The only question I have is there a way that once the random items come up they wont come up again for lets say 10 weeks.

David

Re: Random Cycle Inventory

sunny_talwar — Thu, 04 Feb 2016 13:36:02 GMT

I guess one solution would be to start accumulate a sample in a qvd and may be extract more then 25 values each time and check for where not exists from the accumulated sample.

Re: Random Cycle Inventory

tamilarasu — Fri, 05 Feb 2016 15:10:38 GMT

Yes David. You need to store the sample somewhere as qvd and use where not exists function to load the new samples as said by sunny. You can also assign a flag to identify the week. That would be the best option. Just give a try and let us know.

Re: Random Cycle Inventory

Fri, 05 Feb 2016 15:22:13 GMT

I like your Flag Idea but I have not used them up to now. If you could share an example that would be awesome. I am going to look them up as well.

Thanks,

Re: Random Cycle Inventory

tamilarasu — Mon, 08 Feb 2016 03:59:34 GMT

David, Here you go. Attached sample against our file. Le me know, If you find any issues.

//***************************************************************************
//Max,  Min limit and Max week
Set vMaxlimit = 50;
Set vMinlimit = 25;
Set vMaxWeek  = 10;
//***************************************************************************

//If Backup qvd file exists then 0 else 1
Let vQVDExists =IsNull(QvdCreateTime('Backup\Sample.qvd'));

If vQVDExists = -1 then
Headers:
LOAD * INLINE [
    ItemNumber,Week
];
STORE Headers into [Backup\Sample.qvd] (qvd);
End If

Headers:
Load
ItemNumber,Week
FROM [Backup\Sample.qvd] (qvd);

NoConcatenate
Temp:
LOAD ItemNumber
FROM
[Inventory Items.xlsx]
(ooxml, embedded labels, table is Sheet1);

Let vRandom = (Rand()*(vMaxlimit-vMinlimit)+vMinlimit)/NoOfRows('Temp');

DROP Table Temp;

Concatenate (Headers)
Sample $(vRandom) LOAD ItemNumber,
Week(Today()) as Week
FROM
[Inventory Items.xlsx]
(ooxml, embedded labels, table is Sheet1);

Count:
Load Count(DISTINCT Week) as Count
Resident Headers;

Let vUnique = Peek('Count');

Drop Table Count;

//***************************************************************************

//Reset after 10 weeks
If vUnique > vMaxWeek then
Drop Table Headers;

Headers:
Sample $(vRandom) LOAD ItemNumber,
Week(Today()) as Week
FROM
[Inventory Items.xlsx]
(ooxml, embedded labels, table is Sheet1);
ENDIF

STORE Headers into [Backup\Sample.qvd] (qvd);

//***************************************************************************

Re: Random Cycle Inventory

Tue, 09 Feb 2016 16:32:17 GMT

Thank you so much. That is perfect.