Set analysis

[Sylvain]

Bonjour à tous,

Je suis nouveau sur le Qlik et je bloque sur une mise en forme d'une donnée:

J'ai deux tables A et B pour lesquelles j ai une donnée commune  d'un champ calculé.

table A : référence, Valeur

table B : référence, Acheteur

je souhaiterais faire une somme des valeurs de ma Table A exclusivement des références présentes à la fois dans ma table A et ma table B.

j'ai essayé avec un set analysis et * mais cela ne fonctionne pas ( sum({<référence*référence>}valeur))

j'ai réussi en passant par le script et la fonction LookUp pour transférer les valeurs de la table A sur la table B, mais c'est très long à mettre en forme (800 000 lignes).

Si quelqu'un à une idée, il est le bienvenu.

Merci

Cordialement

sylvain

[Channa]

if i understand properly

you need to make join between tableA and B on script

just use SUM(Value)

[vishsaggi]

There are only two fields coming from both tables? If yes just Load tables like below it will join on common field reference.

table A:
LOAD reference, Value
From TableAname;

table B:
LOAD reference, Buyer
From TableBname;

[Channa]

yes it should be fine

[Sylvain]

hi,

no there is 21 fields in the Table A with 5 calculated fields and 6 fields in a Table B with 1 calculated field,

one calculated field in both tables is common ( Data are almost the same) .

Exemple :

Table A

1 - 2 - 3 -4 -5 -6 ....

Table B

1 - 3 - 4 -8

I try to Concat 2 Tables but it does not work

I think it's not possible to join 2 calculated field in script ( I did not succed)

[Channa]

you will join only on Reference column which is common in both tables

[vishsaggi]

Can you share some sample script or data so that we can look into it in a better way to suggest?

[Sylvain]

Here is my data

It's Excel File. There is 2 Tables.

I would like to know the Total Value of columm (Concat (store,-,reference)) if the data is in the both tables.

I try to create new calculated field in Table A with condition (if) but I doesn't succed, I can't use the Data of another Table.

I try with set Analysis but I didn't found solution.

Thanks for your Help and Sorry for my English 

[Channa]

try

LOAD
"inventory date",
"inventory name",
"store" as StoreA,
"provider number",
provider as providerA ,
reference,
designation as designationA,
location as locationA,
"stock before",
"stock after",
"positive diff",
"negative diff",
"Total diff",
"Concat (store,-,reference)" as ConcatA
FROM [lib://sour/File qlik.xlsx]
(ooxml, embedded labels, table is [Table A]);

LOAD
"store",
reference,
designation,
provider,
location,
STOCK,
PRICE,
"TOTAL VALUE",
"Concat (store,-,reference)" as ConcatB
FROM [lib://sour/File qlik.xlsx]
(ooxml, embedded labels, table is [Table B]);

attach is ur datamodel

just do sum(TOTAL VALUE)

[Sylvain]

Thanks Channa,

but sum(TOTAL VALUE) is the sum of total Value even if the reference is not in the both Table