Load script question

[jduluc12]

Hi

My data looks like this

Main:

LOAD * INLINE [

    key, name, amount

    1, Cust, 0

    1, Cust1, 0

    1, Parent, 1

    2, Cust4, 0

    2, Cust5, 0

    2, Parent, 2

];

What I need is to convert it to look like this:

    key, name, amount

    1, Cust, 1

    1, Cust1, 1

    1, Parent, 1

    2, Cust4, 2

    2, Cust5, 2

    2, Parent, 2

So if the key of any row matches with key of Parent row then the content of the amount column of the parent row should be copied to non-parent row.

for example:

row#1  and row #2 has a key of value 1, so the content of the amount column of row which is a parent with same key (which is row#3 )should be copied to row# 1 and row#2.

So 1 will be copied to the amount column of the row#1 and row#2.

Thanks,

Jean

[sunny_talwar · Accepted solution]

Try this

Main:

LOAD *,

If(name = 'Parent', 0, 1) as Flag;

LOAD * INLINE [

    key, name, amount

    1, Cust, 0

    1, Cust1, 0

    1, Parent, 1

    2, Cust4, 0

    2, Cust5, 0

    2, Parent, 2

];

FinalTable:

LOAD key,

name,

If(key = Previous(key), Peek('amount'), amount) as amount

Resident Main

Order By key, Flag;

DROP Table Main;