Lookup missing values in an existing column

[#1]

Hello!

I am trying to populate a sparse column based on values of the same column. I attached an example.

I have a table like the following:

Id, Before, Group

1, None, 1

2, 1, -

3, None, 2

4, 2, -

5, 4, -

6, 3, -

To fill the missing values I intend to lookup the value in Before in the Id column and receive the value of Group. For example in line 2 i have the value 1 in "Before", so it should look in line 1 (as there is "Id" 1) and return 1 for "Group".

I want to receive the following result:

Id, Before, Group

1, None, 1

2, 1, 1,

3, None, 2

4, 2, 1

5, 4, 1

6, 3, 2

I am using LEFT JOIN right now to lookup the missing groups and I understand that this will not work because the column group already exsits. But in fact I do not know how to deal with it. Can anyone help?

[swuehl]

Maybe like this:

[Data]:

LOAD Id, Before, if(len(trim(Group)),Group, Peek('Group', Before-1) ) As Group;

LOAD *, If(Before = 'None', AutoNumber(Id)) as Group;

LOAD * INLINE [

    Id, Before

    1, None

    2, 1

    3, None

    4, 2

    5, 4

    6, 3

];

[#3]

I am not sure but did you miss anything to compare len(trim(group))?

[Mark_Little]

Hi,

That's the way I was looking to tackle it, beat me to the punch.

Mark

[swuehl]

You can compare against >0, if you want, but it's not necessary.

[#6]

Would the solution work as well for non-numeric Ids, for example if my Ids were something like the following

Id, Before, Group

A1, None, 1

A2, A1, -

A3, None, 2

A4, A2, -

B1, A4, -

B2, A3, -

[swuehl · Accepted solution]

Should work with a little extension:

Table:

LOAD Id, Before, if(len(trim(Group)), Group, Peek('Group', autonumber(Before,'Lookup')-1) ) as Group;

LOAD Id, autonumber(Id,'Lookup') as TMP, Before, If(Before = 'None', AutoNumber(Id)) as Group;

Load * INLINE [

Id, Before,

A1, None,

A2, A1,

A3, None,

A4, A2,

B1, A4,

B2, A3,

];

[#8]

Is there any reason you include the string 'Lookup' for the creation of the autonumber?

[swuehl]

Just to don't mix the AUTONUMBERing of the lookup with the AUTONUMBERing of the first Group generation.

If you remove the 'Lookup' AUTONUMBER id, you'll see that the Group values for 'None' Before records will be different.

[#10]

I am afraid but I don't really understand what you mean. I thought the parameters of the Autonumber() function create an unique number (which is equal for a full load) from the parameters. and as such a parameter I interpreted the word 'lookup'. But I suggest I am wrong with that expectation?