How to link 2 tables, just in a graph and no in the loader ?

[Anonymous]

Hi

I have a problem that is simple, but I don't know why it's doesn't work :

I have the tables :

QV_TOU_CONTACTS

CONTACT NUM     GROUPE_ANNONCE     CA

125325                    TOTO                         100

125326                    TITI                              20

125327                    TOTO                          50

QV_TOU_COUT_VW

DATE          GROUPE_ANNONCE_COUT          COST

01/01/2013     TOTO                                        10

01/02/2023     TITI                                             20

01/03/2013      TOTO                                          30

01/03/2013     TITI                                                 30

01/04/2013     TATA                                            40

I want to make the chart below, and to calculate the sum of COUT where GROUPE_ANNONCE=GROUPE_ANNONCE_COUT :

GROUPE_ANNONCE     CA         COUT

TOTO                              150     10 (10 + 30)

TITI                                   20     50 (20+30)

Thanks for your help

[sparur]

The best way is to connect these tables in the Data Model.

Or yo could only join COST field (and rename to COST2 for example for avoiding connection) to QV_TOU_CONTACTS table and calculate chart based only one table.

or if you have no possibilities to change the script, you could try to use pick(matc()) approach for calculating COST column, like this:

pick(Match(GROUPE_ANNONCE, 'TOTO', 'TITI'),

  sum({<GROUPE_ANNONCE_COUT = {'TOTO'}>} COST),

  sum({<GROUPE_ANNONCE_COUT = {'TITI'}>} COST)

)

[#3]

I don't see why you wouldn't want to join or contatenate the two tables in the script.

You could just concatenate the two table and just rename the GROUPE_ANNONCE_COUT to GROUPE_ANNONCE in the script.

table1:

LOAD

    [CONTACT NUM],

    GROUPE_ANNONCE,

    CA

FROM ....

CONTATENATE ()table1)

LOAD

    DATE,

    GROUPE_ANNONCE_COUT as GROUPE_ANNONCE,

    COST

FROM ....

[er_mohit]

See the attached file

[sparur]

Hi Mohit

It could be used as an option.

but I usually don't recommend to use IF statement inside an aggregation function. It usually has a big impact to the performance dashboard. But if the dashboard is "light" (not so big data), it could be an option.

[Anonymous]

Thanks for your response

But I have more than 1.000 different GROUP_ANNNCE, so I can't use the function pick like you explain

And I really can't link the 2 tables because they are linked with another columns

Is there another way ?

[Anonymous]

Hi

Thanks but with a big data (and we have a "big data") the performance is no good

[sparur]

so you could use JOIN approach (join only COST field to the QV_TOU_CONTACTS.

or try to use my suggestion with pick(Match), although with a big data it isn't good too, but better than sum(if...) approach.