Left Join help!!

[tamilarasu]

Guys,

I have three excel files. First one is source file and rest are price files (Need to map). In source file, I have a unique column named as "ID". In price files, I have "ID" column but the price can be revised many times, so the Id can be repeated. I want to take the first value (First Id value) and left join with the source file. I have tried the below script but in price2 column, the value seems random (not first one). See below.

Main:

LOAD RowNo() as Unique_Field,

     Id,

     Name,

     Type

FROM

[Source File.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Main)

LOAD Id,

     Price

FROM

[Price file.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Main)

LOAD Id,

     Price2

FROM

[Price file - 2.xlsx]

(ooxml, embedded labels, table is Sheet1);

Store Main into Main.qvd;

Drop Table Main;

LOAD *

FROM

Main.qvd

(qvd)

where not Exists(Unique_Field);

Price file

Qv output:

I have attached files for sample. Please guide where I am wrong.

PS: I have around 10 price files like above.

[maxgro]

result

script

Main:

LOAD RowNo() as Unique_Field,

    Id,

    Name,

    Type

FROM

[Source File.xlsx]

(ooxml, embedded labels, table is Sheet1);

Tmp:

LOAD

  rowno() as rn,

  Id,

    Price

FROM

[Price file.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Main)

load Id, Price

Resident Tmp

where Id<>peek(Id)

order by Id, rn;

DROP Table Tmp;

Tmp:

LOAD

  rowno() as rn,

  Id,

    Price2

FROM

[Price file - 2.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Main)

load Id, Price2

Resident Tmp

where Id<>peek(Id)

order by Id, rn;

DROP Table Tmp;

you can also try with mapping load and applymap

[fvelascog72 · Accepted solution]

Hi,

Try with this:

Main:

LOAD RowNo() as Unique_Field,

     Id,

     Name,

     Type

FROM

[Source File.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Main)

LOAD Id,

     firstvalue(Price) as "Price"

FROM

[Price file.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group by Id;

Left Join(Main)

LOAD Id,

     firstvalue(Price2) as "Price2"

FROM

[Price file - 2.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group by Id;;

[Peter_Cammaert]

Two (possible) problems with your example code:

• It doesn't take the first or any other value at all, it just joins all values back to the original ID
• Empty values aren't skipped (sse first id=5 in Procue.xlsx)

Replace every LEFT JOIN with this code to get an idea how you can do this. You may have adjust this example according to your further needs...

PriceTable1:

LOAD RowNo() AS RID, Id, Price FROM [Price file.xlsx](ooxml, embedded labels, table is Sheet1)

WHERE len(trim(Price)) > 0;

LEFT JOIN [Main)

LOAD Id, FirstSortedValue(Price, RID) AS Price

RESIDENT PriceTable

GROUP BY Id;

DROP Table PriceTable;;

Best,

Peter

[tamilarasu]

Hi Massimo,

It's working as expected . But i need to left join 10 excel files, so think the length of the code. If possible can you give me a sample code for mapping load or apply map.

[tamilarasu]

Federico,

Simple and excellent!!

[tamilarasu]

Hi Peter,

Empty values are not a problem (It can be). Your solution also working nice. Thanks a lot.

[tamilarasu]

all and Thx.