script to get the record related to min date for each type(non key field) using QV builtin functions

[#1]

Hello All,

PFB th scenario.

Table 1
Object	Date	Type
O1	D1	1
O2	D2	1
O3	D3	2
Table 2
Article	Object
A	O1
A	O2
B	O1
B	O3
Join
Article	Object	Date	Type
A	O1	D1	1
A	O2	D2	1
B	O1	D1	1
B	O3	D3	2
Required   result
Article	Object	Date	Type
A	O1	D1	1
B	O1	D1	1
B	O3	D3	2

whenever an article has the same Type, the For each article, the Type with the minimum date is to be retained and the rest are to be excluded.

Could you please suggest how this can be achieved in script, without using a group by and by using QV functions.

Thanks!

[anbu1984]

Use Flag = 1 to retain the rows

T1:
LOAD Object ,Date#(Date,'DD/MM/YYYY') As Date ,Type INLINE [
    Object ,Date ,Type 
    O1 , 01/01/2015, 1
    O1 , 15/01/2015, 1
    O3 , 06/01/2015, 2
];




NoConcatenate
T1_F:
Load *,If(Object <> Peek(Object) And Type <> Previous(Type),1,0) As Flag Resident T1 Order by Object,Type,Date;


Join


LOAD * INLINE [
    Article, Object
    A, O1
    A, O2
    B, O1
    B, O3
];


Drop Table T1;

[#3]

Hello Anbu,

Thanks for the reply

Could you modify your input set according to the question i have shared.

It should be O1, O2, O3

and not O1,O1,O3

When changed so, i am not gettting the expected result.

Appreciate ur help!

[anbu1984 · Accepted solution]

Try this

T1:
LOAD Object ,Date#(Date,'DD/MM/YYYY') As Date ,Type INLINE [
    Object ,Date ,Type 
    O1 , 01/01/2015, 1
    O2 , 15/01/2015, 1
    O3 , 06/01/2015, 2
];




Join


LOAD * INLINE [
    Article, Object
    A, O1
    A, O2
    B, O1
    B, O3
];


NoConcatenate
T1_F:
Load *,If(Article <> Peek(Article),1,If(Type <> Previous(Type),1,0)) As Flag Resident T1 Order by Article,Type,Date;


Drop Table T1;