Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community. Hope you guys can help me out with this problem.
I'm loading 2 excels into my .qvf. The second Load is using an IF expression using a field from the first excel.
When trying to load it, an error pops out saying that Field "CMv" was not found.
LOAD
Soc.,
Material,
CMv,
"Valor de venta",
"Valor PV con IVA"
FROM [lib://Agro/MB51 - Movimientos stock del 01.07.15 al 30.06.16.xlsx]
(ooxml, embedded labels, table is Hoja1)
Where Material >= 200000 and Material <= 299999;
LOAD
WERKS,
LGORT as "Cl.valor.",
LGOBE,
if(CMv='Z15' or CMv='Z16' or CMv='Z17' or CMv='Z18' or CMv='Z39' or CMv='Z40' ,LGOBE) as LGOBEGRANOS,
if(CMv='Z05' or CMv='Z06' or CMv='Z43' or CMv='Z44' ,LGOBE) as LGOBECAMPOS,
OIG_ITRFL,
OIB_TNKASSIGN
FROM [lib://Agro/T001L.XLS]
(ooxml, embedded labels, table is Sheet1);
Thank you in advance.
Regards.
The answer is simple, mate
You need to concatenate and/or left join the fields from the first sheet to the table of the second sheet. To do that you must invoke a Field Key to link the two tables. Only then you can do something like this:
Table1:
LOAD
Soc.,
Material,
CMv,
"Valor de venta",
"Valor PV con IVA",
[%Key Field]
FROM [lib://Agro/MB51 - Movimientos stock del 01.07.15 al 30.06.16.xlsx]
(ooxml, embedded labels, table is Hoja1)
Where Material >= 200000 and Material <= 299999;
left join (Table1)
LOAD
WERKS,
LGORT as "Cl.valor.",
LGOBE,
OIG_ITRFL,
OIB_TNKASSIGN,
[%Key Field]
FROM [lib://Agro/T001L.XLS]
(ooxml, embedded labels, table is Sheet1);
Table:
LOAD *,
if(CMv='Z15' or CMv='Z16' or CMv='Z17' or CMv='Z18' or CMv='Z39' or CMv='Z40' ,LGOBE) as LGOBEGRANOS,
if(CMv='Z05' or CMv='Z06' or CMv='Z43' or CMv='Z44' ,LGOBE) as LGOBECAMPOS;
LOAD *
Resident Table1;
Drop Table Table1;
Hope this helps you
Regards,
MB
You will need to bring CMv into your second Excel file. To do that, you have few options
1) Use Lookup function
2) Use ApplyMap function
3) Join the tables together
Does this CMv field exist in your excel sheet?
Hi,
I don't think this will work because it is pulling the data from the spreadsheet and CMv doesn't exist in lib://Agro/T001L.XLS. What I would suggest is to use Apply Map function.
Hope this helps
The answer is simple, mate
You need to concatenate and/or left join the fields from the first sheet to the table of the second sheet. To do that you must invoke a Field Key to link the two tables. Only then you can do something like this:
Table1:
LOAD
Soc.,
Material,
CMv,
"Valor de venta",
"Valor PV con IVA",
[%Key Field]
FROM [lib://Agro/MB51 - Movimientos stock del 01.07.15 al 30.06.16.xlsx]
(ooxml, embedded labels, table is Hoja1)
Where Material >= 200000 and Material <= 299999;
left join (Table1)
LOAD
WERKS,
LGORT as "Cl.valor.",
LGOBE,
OIG_ITRFL,
OIB_TNKASSIGN,
[%Key Field]
FROM [lib://Agro/T001L.XLS]
(ooxml, embedded labels, table is Sheet1);
Table:
LOAD *,
if(CMv='Z15' or CMv='Z16' or CMv='Z17' or CMv='Z18' or CMv='Z39' or CMv='Z40' ,LGOBE) as LGOBEGRANOS,
if(CMv='Z05' or CMv='Z06' or CMv='Z43' or CMv='Z44' ,LGOBE) as LGOBECAMPOS;
LOAD *
Resident Table1;
Drop Table Table1;
Hope this helps you
Regards,
MB
Thank you Miguel.
Got it done.