Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hkeller1331
Contributor
Contributor

Create a calculated field from multiple Data Sources

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:

AcronymCount of Sending transactionsCount of Receiving Transactions
A21
B22
C12

 

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

Labels (3)
1 Solution

Accepted Solutions
mato32188
Specialist
Specialist

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

ECG line chart is the most important visualization in your life.

View solution in original post

3 Replies
mato32188
Specialist
Specialist

Hi,

could you pls share you script?

Thanks.

m

ECG line chart is the most important visualization in your life.
hkeller1331
Contributor
Contributor
Author

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
Specialist
Specialist

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

ECG line chart is the most important visualization in your life.