Create a calculated field from multiple Data Sources

[hkeller1331]

Hello everyone I have two data sources. One is a list of names and their associated acronyms and a second table with information that is being sent and received. I would normally join the acronym table on the backend but the acronym table is referenced twice (1 for sending, 1 for receiving) and when I join it only joins to one of the two new fields.

The output I am looking for is shown below:

Acronym	Count of Sending transactions	Count of Receiving Transactions
A	2	1
B	2	2
C	1	2

The calculated field I am using now for sending transaction is:

COUNT({<Acronym = [Sending Acronym]>} ID_Nbr)

And this seems to almost work but for each cell the output is 5 which is the total of all sent transactions

[mato32188]

Hi,

could you pls share you script?

Thanks.

m

[hkeller1331]

Here is the load script I have:

LOAD

    ID_NBR,

    TITLE,

    "Receiving Acronym",

    "Sending Acronym",

    "Create Date",

    FS_YEAR,

    "Agreement Start Date",

    "Agreement End Date",

    TOTAL_ESTIMATED_AMT,

    TOTAL_DIRECT_COST_AMT,

    TOTAL_REMAINING_AMT,

    GTC_MOD_NBR,

    "Count",

    Subtotal,

    "Total Count",

    "Total $'s",

FROM [lib://AttachedFiles/GTC_Report.xlsx]

(ooxml, embedded labels, table is [Cumulative]);\

LOAD

    Agency,

    Acronym,

    Indicator

FROM [lib://AttachedFiles/ Acronym_Listing.xlsx]

(ooxml, embedded labels, table is Sheet2);

[mato32188 · Accepted solution]

Hi,

what about this?

received:

Load 

"Receiving Acronym" as Acronym,

count(ID_NBR) as "#Received Transactions"

FROM [lib://AttachedFiles/GTC_Report.xlsx]

(ooxml, embedded labels, table is [Cumulative])

group by "Receiving Acronym";

sent:

Load 

"Sending Acronym" as Acronym, 

count(ID_NBR) as "#Sent Transactions"

FROM [lib://AttachedFiles/GTC_Report.xlsx]

(ooxml, embedded labels, table is [Cumulative])

group by "Sending Acronym";

Fact:

noconcatenate

load * resident sent; outer join (Fact) load * resident received; drop tables sent, received;

DIM:

LOAD

    Agency,

    Acronym,

    Indicator

FROM [lib://AttachedFiles/ Acronym_Listing.xlsx]

(ooxml, embedded labels, table is Sheet2);

Then create straight table and add your fields into it. Sorry, if there is any typo, I am writing on the fly.

BR

m