topic Apply Map/ Left Join to get only one value in QlikView

Apply Map/ Left Join to get only one value

Anonymous — Tue, 23 Jan 2018 14:47:45 GMT

Hi,

I have a scenario where in my main table is like:

Account_No BSG Volume

A123 1 100

A123 2 50

A123 3 80

A867 1 1000

Now I have another table which is like :

Account_No Sales

A123 850

A867 900

I have to get the sale values in the main table. The problem I am facing is that if I use a Left Join with Sales table my Sales number will get inflated or duplicated since there are more than one same Account No in the main table. Appply map will go the same.

The desired output is:

Account_No BSG Volume Sales

A123 1 100 850

A123 2 50

A123 3 80

A867 1 1000 950

Re: Apply Map/ Left Join to get only one value

sunny_talwar — Tue, 23 Jan 2018 14:49:36 GMT

May be try this

Table:

LOAD Account_No,

BSG,

Volume

FROM ...

Left Join (Table)

LOAD Account_No,

1 as BSG,

Sales

FROM ....

If all Account_No always have BSG = 1, the above can work for you

Re: Apply Map/ Left Join to get only one value

Anonymous — Tue, 23 Jan 2018 15:43:03 GMT

Thanks Sunny, the above is just a Scenario I created. In actual data I will have lot many account numbers like in thousands. Also I will have other fields which I have not included in the above scenarios

Re: Apply Map/ Left Join to get only one value

Anonymous — Tue, 23 Jan 2018 15:45:25 GMT

And the BSGs will also be many not just 1 or 2. I am ok with mapping the Sales with any BSG

Re: Apply Map/ Left Join to get only one value

sunny_talwar — Tue, 23 Jan 2018 15:47:53 GMT

I understand that, but is one of the BSG value = 1 for all the Account_Nos you have?

Re: Apply Map/ Left Join to get only one value

Anonymous — Tue, 23 Jan 2018 16:08:05 GMT

No it is not going to be always 1. It can be any thing

Re: Apply Map/ Left Join to get only one value

sunny_talwar — Tue, 23 Jan 2018 16:17:31 GMT

May be try this then

Temp:

LOAD Account_No,

Sales

FROM ....

Left Join (Temp)

LOAD Account_No,

Min(BSG) as BSG

FROM ....;

Table:

LOAD Account_No,

BSG,

Volume

FROM ...

Left Join (Table)

LOAD Account_No,

BSG,

Sales

Resident Temp;

DROP Table Temp;

Re: Apply Map/ Left Join to get only one value

Anonymous — Tue, 23 Jan 2018 16:27:28 GMT

Thanks Sunny, this would work perfectly if I have only BSG. I have many other fields which I have not listed in the above example which will again cause sales duplication.

I was thinking of doing like Dense rank partition by (SQL function) Account No and then take Rank 1 in the front end.

Re: Apply Map/ Left Join to get only one value

sunny_talwar — Tue, 23 Jan 2018 16:45:29 GMT

Would you be able to share few rows of data with all columns?

Re: Apply Map/ Left Join to get only one value

pradosh_thakur — Tue, 23 Jan 2018 17:04:05 GMT

Do you want Sales to be associated where volume is max ? There must be some logic right ?

Re: Apply Map/ Left Join to get only one value

MarcoWedel — Thu, 25 Jan 2018 21:01:45 GMT

If there is no association between BSG/Volume and Sales required, you might as well concatenate these tables instead of joining them (or just leave them separated).

hope this helps

regards

Marco