Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
patriciousa
Not applicable

If between excels in load.

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.

1 Solution

Accepted Solutions
miguelbraga
Not applicable

Re: If between excels in load.

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

5 Replies
sunny_talwar
Not applicable

Re: If between excels in load.

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

balrajahlawat
Not applicable

Re: If between excels in load.

Does this CMv field exist in your excel sheet?

sinanozdemir
Not applicable

Re: If between excels in load.

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
Not applicable

Re: If between excels in load.

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
Not applicable

Re: If between excels in load.

Thank you Miguel.

Got it done.