topic Re: Data Intersection in QlikView

Data Intersection

Tue, 16 Apr 2013 22:25:48 GMT

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

Re: Data Intersection

sushil353 — Wed, 17 Apr 2013 05:35:52 GMT

Hi,

Please find the attached modified qvw file.

HTH

Sushil

Re: Data Intersection

kaushiknsolanki — Wed, 17 Apr 2013 06:02:18 GMT

Hi,

Have a look at the attachment.

Regards,

Kaushik Solanki

Re: Data Intersection

jagan — Wed, 17 Apr 2013 06:15:31 GMT

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.

Re: Data Intersection

Anonymous — Wed, 17 Apr 2013 08:14:57 GMT

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,

Re: Data Intersection

Wed, 17 Apr 2013 13:29:25 GMT

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