topic Re: Left Join help!! in QlikView

Left Join help!!

tamilarasu — Mon, 01 Jun 2015 07:02:31 GMT

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.

Re: Left Join help!!

maxgro — Mon, 01 Jun 2015 07:16:50 GMT

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

Re: Left Join help!!

fvelascog72 — Mon, 01 Jun 2015 07:19:23 GMT

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;;

Re: Left Join help!!

Peter_Cammaert — Mon, 01 Jun 2015 07:23:41 GMT

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

Re: Left Join help!!

tamilarasu — Mon, 01 Jun 2015 07:31:18 GMT

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.

Re: Left Join help!!

tamilarasu — Mon, 01 Jun 2015 07:34:05 GMT

Federico,

Simple and excellent!!

Re: Left Join help!!

tamilarasu — Mon, 01 Jun 2015 07:45:56 GMT

Hi Peter,

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

Re: Left Join help!!

tamilarasu — Mon, 01 Jun 2015 07:48:22 GMT

all and Thx.