Join two data sets

[chiranjivdas]

I have two sets of data

Set A has the following (this is SQL pulling from database)

Company (primary key)

Year

Product

Sales Order Number

Revenue

Set B has the following (this is excel file manually managed by me)

Company (primary key)

Industry Segment

Share (% in each industry segment)

I want to join the two sets by loading them in QV so the Share in each Industry Segment applies to the Revenue.

For example, ABC Company has following industry segmentation

Aerospace: 35%

Medical: 25%

Energy: 40%

So if it has a Revenue of $1M, it should show up as

Aerospace: $350K

Medical: $250K

Energy: $400K

I tried to create a new expression (Adjusted Revenue) with = Share * Revenue but it gives erroneous data. Please help.

Thanks,

CD

[felipedl]

Hi Chiranjiv,

For joining purposes just use the join statement like bellow:

SetA:

Load

     Company,

     Year,

     ProductSales,

     Order,

     NumberRevenue

From [SQLConnection];

// This does the join, based on the common fields, in this case the "Company" field.

join (SetA)

Load

     Company

     [Industry Segment],

     Share

From [Excel file];

NoConcatenate

Data:

Load

     // Loads all the fields from the previously created table SetA with the Revenue and Share

     *,

     // Since both fields are in the same table, you can do the calculations now

     Share * NumberRevenue as [Adjusted Revenue]

Resident SetA;

drop table SetA;

Felipe.

[chiranjivdas]

Thanks. It worked. The error was because the formula required me to put the sum function and include the share in the parenthesis.

=sum(Share*[Adjusted Revenue])

[vishsaggi]

Hello Das,

If your issues is resolved can you please close the thread marking correct/helpful responses accordingly.

Thanks,
V.