how to join two table and count

[#1]

Hi, I am new to qlikview and I got a question. I load two table from two servers and I will to join them together and count the num of occurs.

For example, the data like this, I will to count how many times A in table Test2 appears in Test1.

Test1:

Load * INLINE [

    MEMBERID, ATT1

    A, 1
    A, 1
    A, 2
    A, 3
    B, 1
    B, 2
    C, 1
];

Test2:

Load * INLINE [


    MEMBERID
 
   A
   B
   C
];

I want to get some result like this:

A, 4

B, 2

C, 1

How to write the load

Thanks in advance

[#2 · Accepted solution]

Good Morning,

See attached file. I have expanded the first table with Members F - G to show the difference, otherwise you don't need joining the second table, if the members there are the same like in the first table.

Regards

Vicky

[jagan]

Hi,

Try this script

Test1Temp:

Load * INLINE [

    MEMBERID, ATT1

    A, 1

    A, 1

    A, 2

    A, 3

    B, 1

    B, 2

    C, 1

];

INNER JOIN(Test1Temp)

Load * INLINE [

    MEMBERID

   A

   B

   C

];

Test1:

LOAD

    MEMBERID,

    Sum(ATT1) AS ATTSUM

RESIDENT Test1Temp

GROUP BY MEMBERID;

DROP TABLE Test1Temp;

Hope this helps you.

Regards,

Jagan.