Agrr on Peek

[Anonymous]

Hi Everyone,

I have a following table

A     B

1     a

1     b

1     c

2     a

2     b

3     a

and I want a following output

A     B     Flag

1     a     -

1     b     -

1     c     1

2     a     -

2     b     1

3     a     1

Please can someone help me in getting the above output.

Thanking everyone in anticipation

Regards

Karunpreet

[MarcoWedel]

Hi,

maybe something like this?

table1:
LOAD RecNo() as ID, * INLINE [
    A, B
    1, a
    1, b
    1, c
    2, a
    2, b
    3, a
];

Left Join (table1)
LOAD *, 1 as Flag
Resident table1
Where A<>Previous(A)
Order By ID desc;

DROP Field ID;

hope this helps

regards

Marco

[maxgro]

another solution could be

table1: 

LOAD * INLINE [ 

    A, B 

    1, a 

    1, b 

    1, c 

    2, a 

    2, b 

    3, a 

]; 

Left Join (table1) 

LOAD A, MaxString(B) as B, 1 as Flag

Resident table1 

Group By A; 

[MarcoWedel]

can you elaborate on the condition that defines your flag?

regards

Marco

[swuehl · Accepted solution]

Yes, a short explanation how the flag is defined would be more than helpful.

Assuming that it's flagging the last record per dimension A in load order:

table1: 
LOAD * INLINE [ 
    A, B 
    1, a 
    1, b 
    1, c 
    2, a 
    2, b 
    3, a 
]; 

Left Join (table1) 
LOAD A, LastValue(B) as B, 1 as Flag
Resident table1 
Group By A; 

[jagan]

Hi,

Try this script

Data:

LOAD *,

RowNo() AS Sno

INLINE [

    A, B

    1, a

    1, b

    1, c

    2, a

    2, b

    3, a

];

LEFT JOIN(Data)

LOAD

A,

Max(Sno) AS Sno,

1 AS Flag

RESIDENT Data

Group By A;

Regards,

Jagan.