Filter data bases on another column

[vireshkolagimat]

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

[sunny_talwar]

What is the logic here? pick the last value for a date?

[vireshkolagimat]

Hi, for each day i want the latest records by date. For one date there are many transactions but i need the last one.

[sunny_talwar]

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;

[maxgro]

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;

[petter]

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;

[vireshkolagimat]

Hi, Thank you. Its working as expected.

[vireshkolagimat]

Hi, I didn't check it. Will keep posted. Thank you.

[vireshkolagimat]

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.

[sunny_talwar]

I think you tried maxgro‌'s script and not mine