topic Join two data sets in QlikView

Join two data sets

chiranjivdas — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Join two data sets

felipedl — Thu, 31 Aug 2017 13:52:28 GMT

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],

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.

Re: Join two data sets

chiranjivdas — Thu, 31 Aug 2017 21:55:54 GMT

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])

Re: Join two data sets

vishsaggi — Fri, 01 Sep 2017 01:07:29 GMT

Hello Das,

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

Thanks,
V.