topic Re: map sid to all rows. in QlikView

map sid to all rows.

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have below dataset. I am counting the number of distinct SID's for each Location & Country combination.

My requirement is whenever the Location of any SID's is xx, it should be assigned to all the rows in the Data table. I mean in the bottom straight table the 2nd expression should show values as 2,2,1,2.

Data:

LOAD *,Location & '|' & Country as Key

INLINE [

Product, Location, Country, Sales

A, lon, uk, 100

A, york, uk, 150

A, la, us, 200

];

SID:

LOAD SID, Location & '|' & Country as Key

INLINE [

SID, Location, Country

10, lon, uk

20, york, uk

30, xx, us

];

Please help

Re: map sid to all rows.

sunny_talwar — Mon, 13 Nov 2017 14:48:37 GMT

Not sure I understand the logic behing seeing 2,2,1,2... can you elaborate?

Re: map sid to all rows.

surajap123 — Mon, 13 Nov 2017 14:51:59 GMT

Hi Sunny,

I did +1 to existing values in the straight table. Because when the SID with country xx is assigned to complete data in first table, the distinct count of sid goes up.

Re: map sid to all rows.

sunny_talwar — Mon, 13 Nov 2017 15:04:54 GMT

So, when you did +1, did it work? I mean I am still not sure I understand but seems like +1 to your expression will give you what you want, no?

Re: map sid to all rows.

surajap123 — Mon, 13 Nov 2017 15:13:43 GMT

There is only 1 SID with location value as xx in table2, if there are more sid's with xx as location, they all should be assigned to each row in table1.

My requirement is, both tables should link with a proper key. So when a user click for example below values, the output of the expression count(distinct SID) should be 2, instead of 1(ie. SID- 10 and 30)

Product, Location, Country

A, lon, uk

I did cross join like below, but still not working.

join(Data)

load *

resident SID

Where Location = 'xx';