Creating table in qliksense with datas from multiple table

[berryandcherry6]

Hi all,

My client table will be

clientID    ClientName

1                Parlegi

2                Kitkat

3                Nescafe

Batch table

BatchID  BatchName ClientName

2                batch1        Parlegi

3                batch2        Parlegi

4                batch3        Parlegi

6                batch4        Nescafe

7                batch4         Kitkat

8                batch6        Kitkat

9                batch10       Nescafe

Client Data  Table contains

clientID    accessedCount    marketCount      Date_created

1                      2                           2                           2016/2/3

1                     1                            1                           2016/4/6

1                     4                            4                             2016/5/7

2                     5                            8                          2016/2/3

2                     6                            6                             2016/2/9

3                     1                            1                              2016/12/1

1                       2                           1                             2016/11/30

2                      3                             3                           2016/2/3

from all this table   i need to create a single table in qliksense where it contains total of accessedCount  and marketCount for each client with their specific batches, like this

Qliksense Table:

clientID   BatchID   accessedCount      marketCount

1                2                3                             3

1                3                2                             1

1                4                4                             4

2                7               11                            14

2                8                3                             3

3                6                0                              0

3                9                1                              1

Any suggestion.idea,answers , comments  are all appreciated. Please help me on this.

[lakshmikandh]

you can simply use group by and select from [Client Data Table]

select

clientID

BatchID,

sum(accessedCount) as accessedCount,

sum(marketCount) as marketCount

from [Client Data Table]

group by clientID,BatchID

[berryandcherry6]

Hi Lakshmikandh,

Thanks for reply

If BatchID  Column is not present in Client Data Table, then how could i create a {Qliksense table}.

Because in some of my tables i dont be having both clientID and BatchID in same table, by getting clientId or by  clientName i need to recognize which batch belongs to which client, like we do in queries.

[berryandcherry6]

Please guys can anyone could atleast give me some ideas on how i could achieve this?

or is it doable or not in qliksense?

Thanks

[ogster1974]

As you can see in the attached your lack of relationship between batch and the measures is causing your disconnect.

Your Client data table should have BatchId rather than ClientId in it to get the table you want.

Hope this helps

Regards

Andy

[berryandcherry6]

Hi,

Client Data table is a physical table of mysql, so wont be able to change it. It contains no batchID.

Is there a way i could get  batchID from ClientID of Client Table, as we do in queries?

Can i apply Fact table or Star schema over here?

Regards,

[ogster1974]

From what I can see you can relate batches to clients on the Client Name.

YOu need some logical way to derive the Access Counts and Market counts relationship to batches.  Without knowing any of the data context my stab in the dark would be....

A batch is a time based object i.e. has a created and closed date even though you do not show it here and the counts are snapshots in time that a batch was open in.  Could you bucket on Client and BatchId and then apply the counts to the batches on time created?  You could do this in MySQL in queries on during the load.

I've assumed for this to work your batches are sequential.

Hope this helps.

Andy