map sid to all rows.

[surajap123]

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

[sunny_talwar]

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

[surajap123]

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.

[sunny_talwar]

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?

[surajap123]

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