Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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 ?
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.
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
thank you MayilVahanan. I will try tomorrow with my colleagues
from sql I vaguely remember something like "sum(amount) group by %0MAT_SALES". but exactly this didn't work of course
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;