Concatenate two columns from two qvd files

[DyadyaFedor]

Hello.

I am creating an app, using three qvd files.

my script is:

LOAD
    %0MATERIAL,
    %0MAT_SALES
FROM [1.qvd]
(qvd);

Load
    %0MATERIAL,
    "Corporate Reporting Group (Material)"
FROM [2.qvd]
(qvd);

LOAD
    %0MAT_SALES,
     "Local product hierarchy 2",
FROM [3.qvd]
(qvd);

I need a field, which would be a combination of  "Corporate Reporting Group (Material)" and  "Local product hierarchy 2"

I understand I need to write something like "Corporate Reporting Group (Material)"&' '&"Local product hierarchy 2" as NewField, but where and how exactly?

Thank you very much in advance

[Saravanan_Desingh]

Try something like this,

tab1:
LOAD
    %0MATERIAL,
    %0MAT_SALES
FROM [1.qvd]
(qvd);

Left Join(tab1)
Load
    %0MATERIAL,
    "Corporate Reporting Group (Material)"
FROM [2.qvd]
(qvd);

Left Join(tab1)
LOAD
    %0MAT_SALES,
     "Local product hierarchy 2",
FROM [3.qvd]
(qvd);

Left Join(tab1)
LOAD %0MAT_SALES, "Corporate Reporting Group (Material)"&' '&"Local product hierarchy 2" As NewField
Resident tab1;

[DyadyaFedor]

Wow! first of all, thank you. it works.

however, what I can't understand is:

my 1.qvd contains one more field. let's say amount. so the entire script now looks like  

tab1:

LOAD

    %0MATERIAL,

    %0MAT_SALES,

    amount

FROM [1.qvd]

(qvd);

Left Join

Load

    %0MATERIAL,

    "Corporate Reporting Group (Material)"

FROM [2.qvd]

(qvd);

Left Join

LOAD

    %0MAT_SALES,

     "Local product hierarchy 2",

FROM [3.qvd]

(qvd);

Left Join

LOAD "Corporate Reporting Group (Material)"&' '&"Local product hierarchy 2" As NewField

Resident tab1;

What puzzles me Amount is getting outrageously big. So apparently I am doing something wrong. But what ?

[Saravanan_Desingh]

I don't see any issue with the above code. May be you can pull the 'amount' from 1.qvd and see if it is different from what you are seeing.

[MayilVahanan]

HI @DyadyaFedor 

Try like below

MapMaterial:
Mapping
LOAD
%0MAT_SALES,
%0MATERIAL
FROM [1.qvd]
(qvd);

MapCorporate:
Mapping
Load
%0MATERIAL,
"Corporate Reporting Group (Material)"
FROM [2.qvd]
(qvd);

LOAD
%0MAT_SALES,
ApplyMap('MapCorporate',ApplyMap('MapMaterial', %0MAT_SALES, 'NA'),'NA') &'||'& "Local product hierarchy 2" as NewField
FROM [3.qvd]
(qvd);

If you are using join & there is many to many relationship or one-to-Many relationship between tables, values will get duplicates. Hope it helps

[DyadyaFedor]

thank you MayilVahanan. I will try tomorrow with my colleagues

[DyadyaFedor]

from sql I vaguely remember something like "sum(amount) group by %0MAT_SALES". but exactly this didn't work of course

[DyadyaFedor]

Solved.

tab1:

LOAD

    %0MATERIAL,

    %0MAT_SALES,

    amount

FROM [1.qvd]

(qvd);

Left Join

Load

    %0MATERIAL,

    "Corporate Reporting Group (Material)"

FROM [2.qvd]

(qvd);

Left Join

LOAD

    %0MAT_SALES,

     "Local product hierarchy 2",

FROM [3.qvd]

(qvd);

tab2:

LOAD %0MAT_SALES, "Corporate Reporting Group (Material)"&' '&"Local product hierarchy 2" As NewField

Resident tab1;