topic Re: Concatenate two columns from two qvd files in App Development

Concatenate two columns from two qvd files

DyadyaFedor — Mon, 20 Dec 2021 21:02:46 GMT

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

Re: Concatenate two columns from two qvd files

Saravanan_Desingh — Sun, 28 Feb 2021 00:37:43 GMT

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;

Re: Concatenate two columns from two qvd files

DyadyaFedor — Sun, 28 Feb 2021 08:35:12 GMT

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 ?

Re: Concatenate two columns from two qvd files

Saravanan_Desingh — Sun, 28 Feb 2021 23:15:49 GMT

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.

Re: Concatenate two columns from two qvd files

MayilVahanan — Mon, 01 Mar 2021 03:34:35 GMT

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

Re: Concatenate two columns from two qvd files

DyadyaFedor — Mon, 01 Mar 2021 20:45:30 GMT

thank you MayilVahanan. I will try tomorrow with my colleagues

Re: Concatenate two columns from two qvd files

DyadyaFedor — Mon, 01 Mar 2021 20:47:38 GMT

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

Re: Concatenate two columns from two qvd files

DyadyaFedor — Mon, 01 Mar 2021 22:37:12 GMT

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;