Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count the occurrence of field from another table

Hello,

I have tables Table A and Table B. Need to create Table C  as given below.  Appreciate any help with this,

Capture.JPG

2 Replies
sunny_talwar

May be this:

TableA:

LOAD * INLINE [

    Sales Person, Salary

    A, 40000

    B, 20000

    C, 30000

    D, 20000

    E, 30000

    F, 30000

];

TableB:

LOAD * INLINE [

    Date, Order ID, Order Amount,  Sales Person

    03/09/2016, a12345, 500, A

    04/09/2016, a12346, 200, B

    05/09/2016, a12347, 600, A

    06/09/2016, a12348, 300, B

    07/09/2016, a12349, 200, D

    08/09/2016, a12350, 200, A

];

TableCTemp:

NoConcatenate

LOAD *

Resident TableA;

Left Join (TableCTemp)

LOAD [Sales Person],

  Count([Sales Person]) as [No of Sales]

Resident TableB

Group By [Sales Person];

TableC:

NoConcatenate

LOAD [Sales Person],

  Salary,

  Alt([No of Sales], 0) as [No of Sales]

Resident TableCTemp;

DROP Table TableCTemp;

sunny_talwar

Or using Mapping Load:

TableA:

LOAD * INLINE [

    Sales Person, Salary

    A, 40000

    B, 20000

    C, 30000

    D, 20000

    E, 30000

    F, 30000

];

TableB:

LOAD * INLINE [

    Date, Order ID, Order Amount,  Sales Person

    03/09/2016, a12345, 500, A

    04/09/2016, a12346, 200, B

    05/09/2016, a12347, 600, A

    06/09/2016, a12348, 300, B

    07/09/2016, a12349, 200, D

    08/09/2016, a12350, 200, A

];

MappingLoad:

Mapping

LOAD [Sales Person],

  Count([Sales Person]) as [No of Sales]

Resident TableB

Group By [Sales Person];

TableC:

LOAD [Sales Person],

  Salary,

  ApplyMap('MappingLoad', [Sales Person], 0) as [No of Sales]

Resident TableA;