Random Cycle Inventory

[#1]

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

[tamilarasu · Accepted solution]

Hi David,

Try,

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

[sunny_talwar]

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

Best,

Sunny

[petter]

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

[jagan]

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.

[tamilarasu]

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;

[#7]

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

[sunny_talwar]

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.

[tamilarasu]

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.

[#10]

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,