Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jleefjcapital
Not applicable

Creating a ratio using two variables from two separate tables

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;

1 Solution

Accepted Solutions
jontydkpi
Not applicable

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

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
2 Replies
jontydkpi
Not applicable

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

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jleefjcapital
Not applicable

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

Thank you!  It worked.