topic Creating a ratio using two variables from two separate tables in QlikView

Creating a ratio using two variables from two separate tables

jleefjcapital — Mon, 01 Jun 2015 19:01:56 GMT

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;

Re: Creating a ratio using two variables from two separate tables

jonathandienst — Tue, 02 Jun 2015 06:30:17 GMT

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)

Re: Creating a ratio using two variables from two separate tables

jleefjcapital — Tue, 02 Jun 2015 19:47:19 GMT

Thank you! It worked.