Problems concatenate two tables

[richards]

Hello

I have a issue regarding making two tables merge to one. In the Table1 i have the data från the financial system. In Table2  the source file is an Excel spread sheet with other levels/dimension. The use of the spread sheet is to explain the amount from the financial system in other dimensions. The uniqe number in both tables are the verficiation number.

For instance.

In the financial system i have 100 $ explained in for instance two place holders of divison in the organisation. Because the information from the Excel spreadsheet doesn exist in the finacial system i would lik to explain the 100 $ in other dimensions. For instance 50 $ as European sale and 50 $ as other countries of sale.

In the script the following is done.  Load Table1 and mark the amount as Amount1. Concatenate Table2 (the Excel spread sheet) with the Financial data and mark the amount as Amount2. I also make 1 as Excel so it would mark which numbers in the Financial data that should use the spread sheet data. 

In step 2 Table 3 loads everything from the now concatenated table 1 and 2. I also make the formula so that the Amount1 and Amount2 is Amount as If(Excel=1,Amount2,Amount1) as Amount.

There is no issue to resolve this as the amount1 and 2 is amount. There either no problem to present the data. The issue however is that in the Concatenated Table1 or Table3 the data from the Table 1 and 2 is not merging. Do anyone have a solution for this?

The purpose of the merging should be the same as if i would write columns in a spread sheet as

Table 1 VerNr, Organisation, Amount1

Table 2 VerNr, Region, Amount2

Table 3 VerNr, Organisation, Region, Amount

[marcus_sommer]

It's not quite clear what do you mean with the data aren't merged after a concatenating - maybe a join approach fits better to your expectation.

- Marcus

[Gabriel]

@richards 

Did you explicitly type the word CONCATENATE between table1 and table 2? 

[richards]

Yes the concatenate is working. The issue however is that that it doesn´t merge on the same rows. Because of this the amount will be double. I´m getting a double row.

If we use the following example on the tables.

Table 1 VerNr, Organisation, Amount1

The values would for instance be   Vernr = 1, Organisation=Div.Manufact. Amount1=100$

Table 2 VerNr, Region, Amount2

The values would for instance be

Vernr = 1, Region=Europe,Amount2=40$

Vernr=1, Region=America, Amount2=60$

Then table 3 should be.

Vern1=1, Region=America, Organisation=Div.Manufact., Amount 60$

Vern1=1, Region=Europe, Organisation=Div.Manufact., Amount 40$

This is not working, so in my case i wounder why it doesn´t concatenate.

In the real case i have more than three dimension in the financial data. So it would be possible that it´s not merging because the Excel spread sheet doesn´t find the same data in the other table. However my understanding is that the table that should merge when its empty

[marcus_sommer]

Concatenate could not merge data on a row-level else it appends one table to another. To merge data on a row-level you need the above mentioned join-approach whereby in your case it won't be very easy because of the relationship of your tables. Some of these challenges might be bypassed by replacing the join with a mapping.

Personally I would tend to another solution. One way may be to keep both amount-fields separately and using an expression like:

alt(sum(Amount2), sum(Amount1))

within the UI.

Because of the fact that you want only one value you may also remain by a concatenating but you need to apply an appropriate filtering. This may look like:

t: load VerNr, Region, Amount from table2;
    concatenate(t)
    load VerNr, Amount from table1 where not exists(VerNr);

- Marcus

[richards]

I tested the solution, however it doesn work for me.  But i found a similar soloution for this case. https://community.qlik.com/t5/New-to-QlikView/Fetching-multiple-fields-using-applymap/m-p/1293009

This solution actually works almost 100 %. However the second post of America doesn´t show  up.  Any ide´?

[marcus_sommer]

Applymap() returned always the first match from the mapping-table. This means the lookup-values must be unique. In your case it would require some extra-logic whereby I'm not sure if it's worth to go.

I suggest you take a look on the help to exists() to understand how it worked and there are also various posting here in the community available. It may now look like much efforts for your task but exists() is a very powerful feature which is often needed.

Important by exists() is that the evaluation happens against already loaded field-values within the script and it's not related to any table. This means the order of load-statements within a script may have an impact of which field-values does exists.

- Marcus