Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
DyadyaFedor
Contributor II
Contributor II

Concatenate two columns from two qvd files

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

 

Labels (1)
7 Replies
saran7de
Master
Master

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
Contributor II
Contributor II
Author

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 ?

saran7de
Master
Master

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

 

Thanks & Regards,
Mayil Vahanan R
DyadyaFedor
Contributor II
Contributor II
Author

thank you MayilVahanan. I will try tomorrow with my colleagues

DyadyaFedor
Contributor II
Contributor II
Author

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

DyadyaFedor
Contributor II
Contributor II
Author

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;