Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
could you pls share you script?
Thanks.
m
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);
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