topic Merge two numeric variables into one in QlikView

Merge two numeric variables into one

QlickySense — Thu, 08 Jul 2021 07:52:34 GMT

Hello. I would like to merge two numeric variables into one. Please note that they are stored in 2 separate tables

Please find below capture of what I would like to achieve. Tried concatenate load/join but does not work as intended

Thank you for your help and have a nice day!

Re: Merge two numeric variables into one

MayilVahanan — Thu, 08 Jul 2021 08:09:28 GMT

Try like below

T:
Load * Inline
[
Var1
1
2
3
];

Load Var2 as Var1 Inline
[
Var2
1
2
4
];

NoConcatenate
Final:
Load * Resident T Order by Var1;

O/P:

Re: Merge two numeric variables into one

marcus_sommer — Thu, 08 Jul 2021 08:12:01 GMT

Concatenate of both tables should work. That you couldn't see them as multiple records within the UI objects doesn't mean that the records aren't there. You may just check by counting them. If you really want to see them you need to add an unique record-identify within the load like rowno() and using it within the UI table, too.

- Marcus

Re: Merge two numeric variables into one

QlickySense — Thu, 08 Jul 2021 08:44:06 GMT

Hi @marcus_sommer

Thank you for your input. I've tried it like

NewTable:

Load Var1 as new_var FROM Table1

Concatenate

Load Var2 as new_var FROM Table 2

As a result I get NewTable.new_var however counts are not good. Some categories are missing, some categories have bad count, some are fine

@MayilVahananThank you for your input, however I got plenty of categories for each variable. Do I really need to specify it for each?

Re: Merge two numeric variables into one

marcus_sommer — Fri, 09 Jul 2021 06:33:12 GMT

I don't know how do you checked the results from this load but concatenating don't changed the values or is loosing any records. The results may be different from your expectation but this doesn't mandatory mean that the load is wrong else rather that your data are like you see them now.

To check them add recno() and rowno() within the load and display them together with the other fields within a table-box to see which values in which associations are really there.

- Marcus

Re: Merge two numeric variables into one

QlickySense — Fri, 09 Jul 2021 07:37:34 GMT

@marcus_sommer: I had a little investigation and I can say, that it concatenates in a good way if the sheet with concatenation is first one in the scrip. When I move it and before this concat several other tables load, it fails

Any idea why is that? Does the concatenate variables needs to be maintained in first sheet always?

Re: Merge two numeric variables into one

marcus_sommer — Fri, 09 Jul 2021 08:31:20 GMT

By using:

...

Concatenate // the same happens with joins, keep and so o n

Load Var2 as new_var FROM Table 2

the load will be always merged with the directly previous loaded table. But you could - and it's recommended to do it always in this way - specify to which table the merging should go, just by adding:

NewTable:

Load Var1 as new_var FROM Table1

Concatenate(NewTable)

Load Var2 as new_var FROM Table 2

and then the load-order isn't so important anymore unless that the target-table must be already loaded/existing.

- Marcus

Re: Merge two numeric variables into one

QlickySense — Fri, 09 Jul 2021 14:05:27 GMT

HI @marcus_sommer,

Thank you, however I dont know what I am doing wrong

NewTable:
LOAD beca_rtn5 as NewVar from $(PEF_Data);

Concatenate(NewTable)

LOAD beca_rtn3 as NewVar from $(TEF_Data);

as In result I got New Table with variables : NewTable.NewVar and PEF_Data
Works when I got this tab as a first though. Do i really need to maintain this order?

Re: Merge two numeric variables into one

marcus_sommer — Fri, 09 Jul 2021 14:17:01 GMT

This "... I got New Table with variables : NewTable.NewVar ..." indicates that there is any qualifying enabled. I'm not sure if this caused your issue but I suggest to remove the qualifying (renaming of fields should be always done explicitly).

In my experience is a sensible use of it very rare and quite often it caused a lot of trouble (nothing what couldn't be solved - but why should I solve problems which I don't have if I wouldn't use this feature ...).

- Marcus

Re: Merge two numeric variables into one

QlickySense — Fri, 09 Jul 2021 14:32:26 GMT

Hi,
Indeed, there are qualifiers. I've done it to distinguish the source (table) of variable - dataset is really huge and there are overlaps on variable names across the tables so it totally ruin the counts or other calculations. If there is no qualify - I have to ran every available chart and change its expression I guess.

How to distinguish calculation, for example I got NameVar1 in both table A and B so Qlik automatically merge the variable.. It is something that can be done by set analysis like COUNT({$ <table={'tableA'}>} NameVar1) ? However I am very scared that entire dashboard will fall off (I've get it to do some adjustments, I am not a creator)

Is there really any way to remove Qualify but keep variables as they are? I think about something along the lines:

Table1:
variables
FROM source1

NoConcatenate
Table2:
variables
FROM source2

and so on? would it work?

Re: Merge two numeric variables into one

marcus_sommer — Fri, 09 Jul 2021 14:56:10 GMT

Yes, removing the qualify may have a big effect within the script and all the UI dimensions/expressions and the resulting efforts to adjust everything might be really huge and even greater as re-creating the application from the scratch. Nevertheless I suggest to consider the last very carefully because maintaining a bad designed application could become a bigger nightmare as starting at the beginning again by applying all learnt things from the last months/years ...

In your special case you may directly before this load set an:

unqualify *;

and afterwards you enables it again with:

qualify *; // respectively with those fields which should be regarded for it

- Marcus