Adding values from another table based on ID

[johnnyjohn]

So say I have the following files

Table1

Table2

I am loading them into Qliksense. 

Basically, when loading Table2, I am trying to add values from Table1 in the following way:

• For each ID (a1, a2, etc...), I need to add a row to Table2 using values from Table1
• In Table1, I'm always going to have an ID show up once, and it will have a value associated to it (here called extra_value). 
• In my Table2, I need to add, for each ID, at max(depth + 1), the value found in Table1 with the corresponding ID.

i.e. the extra_value of a1 in Table1 would be added to Table2 under the ID a1; and with value=0.54 and depth=3

so the final table would look like this (rows added are highlighted)

 

Stumped on this one, any help would be appreciated

[agigliotti]

Hi @johnnyjohn ,

You could use a simple "concatenate" statement like below:

load * from Table1;
concatenate
load * from Table2;

I hope it helps.
Andrea

[MayilVahanan]

Hi @johnnyjohn 

Try like below

T1:
LOAD * Inline
[
id, value, depth
a1, 0.12, 1
a1, 0.23, 2
a2, 0.15, 1
a2, 0.87, 2
];

Concatenate(T1)
Load * Inline
[
id,extra_value
a1, 0.54
a2, 0.87
];

Load id, if(IsNull(value), extra_value, value) as value, If(IsNull(depth), Peek('depth')+1, depth) as depth Resident T1
order by id;

[Brett_Bleess]

@MayilVahanan You have received two very good possible solutions, we would appreciate it if you would return to the thread and use the Accept as Solution button on the post(s) that did help, or if you did something different, you can create a new post about that and then use the button on that post to mark the thread as solved.  If you require further help, please leave an update.

Regards,
Brett

[MayilVahanan]

Hi @Brett_Bleess 

I can't accept the solution and close this thread , because it's not create by me 😉

Hi @johnnyjohn 

Can you please close this thread.

[edwin]

another solution that will include the depth column but not using peek

noconcatenate
extra:
load ID, max(Depth) as Depth resiodent table2 group by ID;

inner join (extra) load ID, ExtraValue as Value resident table1;

concatenate(table2) load ID, Value, Depth resident extra;

drop table extra;

[Brett_Bleess]

@MayilVahanan My apologies, I miss-clicked and got your ID instead of @johnnyjohn very sorry about that!  Thanks for flagging it to @johnnyjohn to respond and close it out.

Regards,
Brett