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]

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

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

[#3]

Thank you so much.  That is perfect.