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!

[mrossoit]

Try to add:

RESULT:

Load distinct Article,

min(Date) as Date,

Type

Resident MY_JOINED_TABLE;

left join

load *, '' as tmp resident MY_JOINED_TABLE;

drop table MY_JOINED_TABLE;

drop field tmp;

Regards

MR

[anbu1984]

Table2:

Load Article,Object From Table2;

Inner Join(Table2)

Load Object,Min(Date) As Date,Type From Table1 Group by Object,Type;

[bismart]

T1:

LOAD * INLINE [

    Object ,Date ,Type

    O1 , 01/01/2015, 1

    O1 , 15/01/2015, 1

    O3 , 06/01/2015, 2

];



Join

LOAD * INLINE [

    Article, Object

    A, O1

    A, O2

    B, O1

    B, O3

];



T2:

NoConcatenate Load

*,

Article & '-' & Type as Key

Resident T1;

DROP TABLE T1;



Table:

Noconcatenate Load Distinct

Key,

Article,

Object,

Min(Date) as Date,

Type

resident T2

where LEN(Date)<>0

group by

Key,

Article,

Object,

Type;


DROP TABLE T2;

[#5]

Hello All,

Thanks for your replies,

I want to avoid group by and do it using QV built in functions.

Is there a way?

[#6]

Hello All,

Thanks for your replies,

I want to avoid group by and do it using QV built in functions.

Is there a way?

[mrossoit]

What do you mean with QV built in functions?

Group by belongs to QV loading syntax.

Are you trying to do it directly in your sheet object avoiding scripting elaborations?

[#8]

Hello Rosso,

I mean, i wanted to use some if and peek condition and set a flag value.

And based on the flag value, i want to identify which records are to be retained and which are to be excluded.

Something like this.

Thanks!

[mrossoit]

I think you can't do it without using Group by. You have to identify rows with minimum date for the same type and to do this you need to use min() which is an aggregation function.

Aggregation functions need Group by to work.

Regards

MR

[#10]

This is not yielding correct output.

Its giving all the records of table1