topic Re: Adding values from another table based on ID in QlikView

Adding values from another table based on ID

johnnyjohn — Fri, 15 Nov 2024 23:43:19 GMT

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

Re: Adding values from another table based on ID

agigliotti — Thu, 29 Oct 2020 08:19:56 GMT

Hi @johnnyjohn ,

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

load * from Table1;
concatenate
load * from Table2;

I hope it helps.
Andrea

Re: Adding values from another table based on ID

MayilVahanan — Thu, 29 Oct 2020 08:31:14 GMT

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;

Re: Adding values from another table based on ID

Brett_Bleess — Wed, 11 Nov 2020 20:37:04 GMT

@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

Re: Adding values from another table based on ID

MayilVahanan — Wed, 11 Nov 2020 23:55:13 GMT

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.

Re: Adding values from another table based on ID

edwin — Thu, 12 Nov 2020 00:50:04 GMT

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;

Re: Adding values from another table based on ID

Brett_Bleess — Thu, 12 Nov 2020 12:09:08 GMT

@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