First occurances

[#1]

Hi All,

I have table like this:

FAMILY	TYPE	PART	ACTION	FINAL	QUANTITY
100	A 2.5	A1	ACT A	Y	70
100	A 3.0	A2	ACT B	N	100
200	B 2.5	B1	ACT F	Y	10
200	B 6.5	B3	ACT M	Y	0
100	A 9.0	A5	ACT A	Y	20

I want do get in another table only the first occurance of each family. In this example, the result table will be:

FAMILY	TYPE	PART	ACTION	FINAL	QUANTITY
100	A 2.5	A1	ACT A	Y	70
200	B 2.5	B1	ACT F	Y	10

Thanks in advance,

Itsik

[Nicole-Smith]

In your load script, you can add a flag like this:

Data:

LOAD *, exists(FAMILY, FAMILY) as FirstValueFlag INLINE [

    FAMILY, TYPE, PART, ACTION, FINAL, QUANTITY

    100, A 2.5, A1, ACT A, Y, 70

    100, A 3.0, A2, ACT B, N, 100

    200, B 2.5, B1, ACT F, Y, 10

    200, B 6.5, B3, ACT M, Y, 0

    100, A 9.0, A5, ACT A, Y, 20

];

And then you can use set analysis like {<FirstValueFlag={0}>} in your expression in order to only get the first occurences.

I have also attached an example.

[#3]

I'm trying do this trick on not INLINE table without success...

[Nicole-Smith]

Please post the load script for your table.

[#5]

I have this SQL Statement:

Load

     FAMILY,

     TYPE,

     PART,

     ACTION,

     FINAL,

     QUANTITY

Resident Test_tbl;

That create the PP table.

I want to do this manipulated that u posted on this statement...

[Nicole-Smith]

What is the SQL statement?  That is a resident load.

[maxgro · Accepted solution]

Source:

load rowno() as ID, *;

LOAD * INLINE [

    FAMILY, TYPE, PART, ACTION, FINAL, QUANTITY

    100, A 2.5, A1, ACT A, Y, 70

    100, A 3.0, A2, ACT B, N, 100

    200, B 2.5, B1, ACT F, Y, 10

    200, B 6.5, B3, ACT M, Y, 0

    100, A 9.0, A5, ACT A, Y, 20

];

Table:

NoConcatenate

load * resident Source where peek(FAMILY) <> FAMILY order by FAMILY, ID;

DROP Table Source;