topic Re: Filter data bases on another column in QlikView

Filter data bases on another column

vireshkolagimat — Sun, 14 Jan 2018 17:18:09 GMT

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

Re: Filter data bases on another column

sunny_talwar — Sun, 14 Jan 2018 17:47:46 GMT

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

Re: Filter data bases on another column

vireshkolagimat — Sun, 14 Jan 2018 17:51:37 GMT

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

Re: Filter data bases on another column

sunny_talwar — Sun, 14 Jan 2018 18:00:21 GMT

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;

Re: Filter data bases on another column

maxgro — Sun, 14 Jan 2018 18:42:59 GMT

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;

Re: Filter data bases on another column

petter — Sun, 14 Jan 2018 19:05:43 GMT

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;

Re: Filter data bases on another column

vireshkolagimat — Mon, 15 Jan 2018 09:08:22 GMT

Hi, Thank you. Its working as expected.

Re: Filter data bases on another column

vireshkolagimat — Mon, 15 Jan 2018 09:09:20 GMT

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

Re: Filter data bases on another column

vireshkolagimat — Mon, 15 Jan 2018 12:20:37 GMT

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.

Re: Filter data bases on another column

sunny_talwar — Mon, 15 Jan 2018 12:28:07 GMT

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