DIVIDE ONE Column value to another column value of Another table

[sunil_sawargave]

Hello Community,

Here i have two table, Which created from two tables which i have performed join operation & created following first table , Having "DATE" , "SHOP NAME" & "SUM OF SCRAP COST".

The Next Second Following table Having "SHOP NAME" "MONTH" "YEAR" & "EQ. VALUE".

In this table " SUM EQ. VALUE " Column Generated After performing Unpivot operation on that table.

Here I want to divide "SCRAP COST" DIVIDE BY "Eq. VALUE"

Thanks & regards

Sunil

[RafaelBarrios]

Hi @sunil_sawargave 

assuming that the division must be done when not only the name of the table matches, but also the date of the first table with the month and year of the second.

I would make a composite key to connect both tables
I first table 

[SHOP NAME]&UPPER(text(date(DATE,'YYYYMMM'))) as key_field

and in second table

[SHOP NAME],YEAR,MONTH as key_field

dont load the [SHOP NAME] in one of the table, so you don’t get a synthetic table.

Now, in visualization formula, as you can have more than one value for a combination of shop name, year and month, you will need to decide what to do in each table

Select a table object

dimensions

[SCRAP COST], YEAR,MONTH

Measure

=sum([SCRAP COST])/sum([Eq. VALUE])

un saludo,

[sunil_sawargave]

Hello Rafael,

Thank you for your replay, Actually my problem is like that, hope you Understand.

I have One Excel Sheet Which i upload from QVD Name is Sheet1 like that following.

Next I have one Table SHOPCOPQ upload from Database in Qlik sense  which is in format like,

Here  i have rename the column as per requirement :BUDAT_S As "DATE" & GRUND_S as "REASON CODE" & KOSTL_S As "COST CENTER" &  DMBTR_F as "SCRAP COST"

So here I have to do left Join in these two sheet with SHOPCOPQ mapping is "COSTCENTER" Center of Excel SHEET & Table "SHOPCOPQ" , which done & table is like

Here i have to do in above Table  SUM of "SCRAP COST" with Having Same "SHOP NAME" with same " DATE" & same "REASON CODE" Condition which is Completed.

Next I have Next Table which is upload from Database "Eqe_VEICLEMASTER_MONTHLY" & then Unpivot that table as per requirement the table is like that.

Here in Above table "DAY " & "EQ. VEHICLE VALUE " Field generated after performing UNPIVOT operation.

In that above table I have sum of "EQ.Value"  As per same shop name on monthly basis.

So i want do divide the "SUM of SCARP COST"  of table 1 By "SUM of EQ. Value" of table two.

for that how to write the script & where to write(Create new script in data load editor or write in existing script).

Thanks & regards

Sunil Sawargave