2 Replies Latest reply: Jun 2, 2015 3:47 PM by Jes Lee RSS

    Creating a ratio using two variables from two separate tables

    Jes Lee

      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;