topic Re: how to join two table and count in QlikView

how to join two table and count

Fri, 24 Aug 2012 05:01:11 GMT

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

Re: how to join two table and count

Fri, 24 Aug 2012 06:16:52 GMT

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

Re: how to join two table and count

jagan — Fri, 24 Aug 2012 06:23:30 GMT

Hi,

Try this script

Test1Temp:

Load * INLINE [

MEMBERID, ATT1

A, 1

A, 2

A, 3

B, 1

B, 2

C, 1

];

INNER JOIN(Test1Temp)

Load * INLINE [

MEMBERID

];

Test1:

LOAD

MEMBERID,

Sum(ATT1) AS ATTSUM

RESIDENT Test1Temp

GROUP BY MEMBERID;

DROP TABLE Test1Temp;

Hope this helps you.

Regards,

Jagan.