Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
MVP
MVP

Re: Data Intersection

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.

5 Replies
sushil353
Honored Contributor II

Re: Data Intersection

Hi,

Please find the attached modified qvw file.

HTH

Sushil

Re: Data Intersection

Hi,

     Have a look at the attachment.

Regards,

Kaushik Solanki

MVP
MVP

Re: Data Intersection

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.

s_varbanov
New Contributor II

Re: Data Intersection

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

Re: Data Intersection

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

Community Browser