If between excels in load.

[patriciousa]

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.

[sunny_talwar]

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

[Anonymous]

Does this CMv field exist in your excel sheet?

[sinanozdemir]

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

[miguelbraga · Accepted solution]

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

[patriciousa]

Thank you Miguel.

Got it done.