Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have attached a sample data and provided and input and output data.
Can you somebody help me to achieve the same.
Regards,
Viresh
What is the logic here? pick the last value for a date?
Hi, for each day i want the latest records by date. For one date there are many transactions but i need the last one.
Try this
Table:
LOAD PRODUCTKEY,
WAREHOUSECODE,
PRODUCT_CODE,
STOCK_DATE,
OPENING_STOCK,
TRANSACTION_QTY,
CLOSING_STOCK,
AutoNumber(RowNo(), PRODUCTKEY&STOCK_DATE) as Key
FROM
[..\..\..\Downloads\stock data.xls]
(biff, embedded labels, table is input$);
Right Join(Table)
LOAD PRODUCTKEY,
STOCK_DATE,
Max(Key) as Key
Resident Table
Group By PRODUCTKEY, STOCK_DATE;
Table:
LOAD PRODUCTKEY,
WAREHOUSECODE,
PRODUCT_CODE,
STOCK_DATE,
OPENING_STOCK,
TRANSACTION_QTY,
CLOSING_STOCK,
AutoNumber(RowNo(), PRODUCTKEY&STOCK_DATE) as Key
FROM [stock data.xls] (biff, embedded labels, table is input$);
Final:
NoConcatenate LOAD *
RESIDENT Table
Where
PRODUCTKEY <> Peek('PRODUCTKEY')
or STOCK_DATE <> Peek('STOCK_DATE')
Order By PRODUCTKEY, STOCK_DATE desc, Key desc;
DROP Table Table;
Since we are into showing different approaches I might just as well throw in mine :
One load without any RESIDENT tables:
DATA:
LOAD
FirstSortedValue(PRODUCTKEY, -RecNo()) AS PRODUCTKEY,
FirstSortedValue(WAREHOUSECODE, -RecNo()) AS WAREHOUSECODE,
FirstSortedValue(PRODUCT_CODE, -RecNo()) AS PRODUCT_CODE,
STOCK_DATE,
FirstSortedValue(OPENING_STOCK, -RecNo()) AS OPENING_STOCK,
FirstSortedValue(TRANSACTION_QTY, -RecNo()) AS TRANSACTION_QTY,
FirstSortedValue(CLOSING_STOCK, -RecNo()) AS CLOSING_STOCK,
FirstSortedValue(comments, -RecNo()) AS comments
FROM
(biff, embedded labels, table is input$) GROUP BY
STOCK_DATE;
Hi, Thank you. Its working as expected.
Hi, I didn't check it. Will keep posted. Thank you.
Hi Sunny,
I need your help to get the opening and closing stock. I tried the attached script but i am getting only the opening and closing stock but no TransactionQty like the below image.
Please have a look and do the needful.
I think you tried maxgro's script and not mine