Data Intersection

[#1]

Hi,

I have two tables: transactions and platform. Here's a sample:

transactions:

LOAD * INLINE [

transaction, site, platform

1, 1, 0

2, 1, 1

3, 1, 1

4, 2, 0

5, 2, 0

6, 2, 0

7, 3, 1

8, 3, 0

;

platform:

LOAD * INLINE [

platform, platform_name

0, new

1, old

;

I would like to limit the transactions table to only include sites which have transactions on both platforms , 0 and 1. In this case, I would only import data for sites 1 adn 3, since site 2 only has sales on plaform 0. Does anyone know how I can specify this in my import script?

Thanks in advance for the help, it is very appreciated. I've attached a qvw with the sample data shown above.

Best,

Matt

[sushil353]

Hi,

Please find the attached modified qvw file.

HTH

Sushil

[kaushiknsolanki]

Hi,

     Have a look at the attachment.

Regards,

Kaushik Solanki

[jagan · Accepted solution]

Hi Matt,

Try this script, hope this helps you.

Transactions:

LOAD * INLINE [

    transaction, site, platform

    1, 1, 0

    2, 1, 1

    3, 1, 1

    4, 2, 0

    5, 2, 0

    6, 2, 0

    7, 3, 1

    8, 3, 0

    9, 3, 0

    10, 3, 0

];

INNER JOIN (Transactions)

LOAD

    site

WHERE platformcount > 1;

LOAD   

    site,   

    Count(Distinct platform) as platformcount

RESIDENT Transactions

GROUP BY site;

platform:

LOAD * INLINE [

    platform, platform_name

    0, new

    1, old

];

Regards,

Jagan.

[Anonymous]

Hi Matt,

there's another solution

transactions:

LOAD * INLINE [

transaction, site, platform

1, 1, 0

2, 1, 1

3, 1, 1

4, 2, 0

5, 2, 0

6, 2, 0

7, 3, 1

8, 3, 0

];

platform:

LOAD * INLINE [

platform, platform_name

0, new

1, old

];

t1:

LOAD distinct site

Resident transactions

Where platform = 0;

Right Join (t1)

LOAD distinct site

Resident transactions

Where platform = 1;

Right Join (transactions)

LOAD site

Resident t1;

DROP Table t1;

Best Regards,

SV

[#6]

Thanks to all for the help. All of these methods worked. Based on what I'm doing, Jagan's will be the best for the moment.

Thanks again, the help was much appreciated.

Matt