create new column that dont exist in qvd file + fill with values.

[janus2021]

Hi.
Many questions on a new topic.
If I load a two tables alike:
Sports:
Load kol1, kol2, kol3
kol1 & '|' & kol2 & '|' & kol3 as key
from test.qvd

Sport2:
Load kol1, kol2,
'-1' as kol3
kol1 & '|' & kol2 & '|' & kol3 as key
from ny.qvd

How do I add a new column: kol3 in the table Sport2 as ex has the value -1 in all fields?
I have tried with inline etc. but get no values ​​in the column.
Using '-1' as kol3 when loading is OK but givs an error using it in a key when linking the tables.
Will link the tables via "Linked table", will not use concatinate. Therefore, I need to be able to create a filled column that is not in qvd.
Is it possible or what am i doing wrong ??

Problem 1: adding a column that is not in the qvd-file to a table.

[Vegar]

Observe that your second load will autoconcatenate into the first because of the similar field names in your tables. To avoid autoconcatenation use noconcatenate before loading .

The load is not aware of kol3 until the table is loaded,  so you can not refere to that column in the where statement.  Try this instead.

NoConcatenate Load kol1, kol2,
'-1' as kol3
kol1 & '|' & kol2 & '|-1'  as key
from ny.qvd

[JuanGerardo]

Hi @janus2021, I think the best way is to use SubField() function to split the different codes, then join with descriptions, and eventually use Concat() to get what you want:

Conf:
Load *
Inline [
ID,ConfigurationCode
344,1;2;3
632,2;3;4
874,
521,4
];

Items:
Load *
Inline [
Item,Description
1,Description 1
2,Description 2
3,Description 3
4,Description 4
];

Inner Join(Items)
Load
ID,
ConfigurationCode,
SubField(ConfigurationCode, ';') AS Item
Resident Conf;

Result:
Load
ID,
ConfigurationCode,
Concat(Description, ';') AS ConfigurationDescription
Resident Items
Group By ID, ConfigurationCode;

Drop Tables Conf, Items;

JG

[janus2021]

Despite the suggestions, I can not make it work.
In the scripteditor i have different sektions that is loading from fact table and dimensions:

Sektion1:
Sports:
Load kol1, kol2, kol3
kol1 & '|' & kol2 & '|' & kol3 as key
from test.qvd

Sektion2:
Sport2:
Load kol1, kol2,
'-1' as kol3
kol1 & '|' & kol2 & '|' & kol3 as key
from ny.qvd

SektionLink:
Link:
Load Distinct
kol1 & '|' & kol2 & '|' & kol3 as key,
kol1 & '|' & kol2 & '|' & kol3 as tmp_key,
kol1, kol2, kol3
from [lib://test.qvd] (qvd);

Concatenate(Link)
Load Distinct
kol1 & '|' & kol2 & '|' & kol3 as key,
kol1, kol2, kol3
from [lib://ny.qvd] (qvd);

its when loading: Concatenate(Link)....
as I get the error: can't find kol3 in ny.qvd...
It is the column I made in Sektion2 and is not in qvd.

[marcus_sommer · Accepted solution]

You try to call kol3 within the key-generation of the second load and this lead to the error because all accessed fields must already exists within the source - defining/renaming new fields within the current load-statement doesn't mean that you could use them directly. This means in your case you need to apply:

Sektion2:
Sport2:
Load kol1, kol2,
'-1' as kol3
kol1 & '|' & kol2 & '|' & '-1' as key
from ny.qvd

or maybe using a preceding load like:

Sektion2:
Sport2:
load *, kol1 & '|' & kol2 & '|' & kol3 as key;
Load kol1, kol2,
'-1' as kol3
from ny.qvd

Further like the others already mentioned without specifying a noconcatenate both tables would be auto-concatenated - at least with the shown fields in your example. If your tables have further different fields it wouldn't happens.

Beside this I suggest to re-think your whole approach carefully because it looked that you intends to link two fact-tables directly with a complex key or with a link-table. Quite often it's neither the easiest approach to develop a datamodel nor to get the most performant UI. The recommended approach is to develop the datamodel in the direction of a star-scheme by concatenating the fact-tables.

- Marcus