Creating a ratio using two variables from two separate tables

[jleefjcapital]

Hello,

I am trying to construct a ratio of two variables residing in two separate load statements.  I started by concatenating the two tables based on a common column- Date.   Then I referenced the variables to construct the ratio and referenced the concatenated table using "resident".  The output values are all zero.  I'm not sure what I'm doing wrong. 

Here is my code:

Pop_Tax:

LOAD

    Date(Date#("DATE",'YYYY'),'YYYY') AS DATE,

    US as [Pop_US]

FROM [lib://Demographics/Population by state.xlsx]

(ooxml, embedded labels, table is US);

CONCATENATE(Pop_Tax)

LOAD

    Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY') AS DATE,

    value*1000 as USTax

  FROM [lib://State Government Tax Collections, Total Taxes in the United States]

(XmlSimple, table is [observations/observation]);

Tax_percap:

LOAD

DATE,

Div((USTax),[Pop_US]) as [USTAX_PerCap],

STATE

Resident Pop_Tax;

[jonathandienst · Accepted solution]

You need a JOIN for this analysis:

Pop_Tax:

LOAD

    Date(Date#("DATE",'YYYY'),'YYYY') AS DATE,

    US as [Pop_US]

FROM [lib://Demographics/Population by state.xlsx]

(ooxml, embedded labels, table is US);

Join 

LOAD

    Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY') AS DATE,

    value*1000 as USTax

  FROM [lib://State Government Tax Collections, Total Taxes in the United States]

(XmlSimple, table is [observations/observation]);

(Change to left join or inner join according to your requirements)

[jleefjcapital]

Thank you!  It worked.