Merge two numeric variables into one

[QlickySense]

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!

[MayilVahanan]

HI

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:

[marcus_sommer]

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 

[QlickySense]

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?

[marcus_sommer]

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

[QlickySense]

@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?

[marcus_sommer · Accepted solution]

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

[QlickySense]

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?

[marcus_sommer]

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

[QlickySense]

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?