Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data Intersection

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

5 Replies
sushil353
Master II
Master II

Hi,

Please find the attached modified qvw file.

HTH

Sushil

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Have a look at the attachment.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
jagan
Luminary Alumni
Luminary Alumni

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
Not applicable
Author

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

Not applicable
Author

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