topic Re: Merging data from multiple API connections & Adding new column in QlikView

Merging data from multiple API connections & Adding new column

jleefjcapital — Thu, 21 May 2015 14:03:19 GMT

I am trying to merge data from two API connections. The code looks like this -

LOAD

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

value as "REAL GDP WISCONSIN"

FROM [lib://Real Total Gross Domestic Product by State for Wisconsin]

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

LOAD

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

value as "REAL GDP WYOMING"

FROM [lib://Real Total Gross Domestic Product by State for Wyoming]

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

LOAD

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

value as "REAL GDP WEST VIRGINIA"

FROM [lib://Real Total Gross Domestic Product by State for West Virginia]

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

As it stands, these are three separate time series data names Real GDP Wisconsin, Real GDP Wyoming, and Real GDP Virginia.

I would like to merge them into a single dataset with a different name.

Once I do this, I would like to add a new column for the State name (to add a location dimension for mapping).

Thank you,

Jessica

Re: Merging data from multiple API connections & Adding new column

sunny_talwar — Thu, 21 May 2015 14:14:22 GMT

Goal is to get a table like this??

Date	State	Real GDP

If yes then you can try this:

Data:

LOAD

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

value as [REAL GDP]

'Wisconsin' as State

FROM [lib://Real Total Gross Domestic Product by State for Wisconsin]

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

Concatenate(Data)

LOAD

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

value as [REAL GDP]

'Wyoming' as State

FROM [lib://Real Total Gross Domestic Product by State for Wyoming]

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

Concatenate(Data)

LOAD

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

value as [REAL GDP]

'West Virgina' as State

FROM [lib://Real Total Gross Domestic Product by State for West Virginia]

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

HTH

Best,

Sunny

Re: Merging data from multiple API connections & Adding new column

jleefjcapital — Thu, 21 May 2015 14:20:58 GMT

This is the error I get....

The following error occurred:

Syntax error, missing/misplaced FROM: Data: LOAD Date#("date",'YYYY-MM-DD') AS DATE, value as [REAL GDP] 'Wisconsin' as State FROM [lib://Real Total Gross Domestic Product by State for Wisconsin] (XmlSimple, table is [observations/observation])

The error occurred here:

Data: LOAD Date#("date",'YYYY-MM-DD') AS DATE, value as [REAL GDP] 'Wisconsin' as State FROM [lib://Real Total Gross Domestic Product by State for Wisconsin] (XmlSimple, table is [observations/observation])

Data has not been loaded. Please correct the error and try loading again.

Re: Merging data from multiple API connections & Adding new column

jonathandienst — Thu, 21 May 2015 14:24:01 GMT

value as [REAL GDP]

should read

value as [REAL GDP],

(trailing comma)

Re: Merging data from multiple API connections & Adding new column

sunny_talwar — Thu, 21 May 2015 14:24:22 GMT

Forgot to add comma (,) after [REAL GDP]. Give it a try now.

Data:

LOAD

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

value as [REAL GDP],

'Wisconsin' as State

FROM [lib://Real Total Gross Domestic Product by State for Wisconsin]

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

Concatenate(Data)

LOAD

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

value as [REAL GDP],

'Wyoming' as State

FROM [lib://Real Total Gross Domestic Product by State for Wyoming]

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

Concatenate(Data)

LOAD

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

value as [REAL GDP],

'West Virgina' as State

FROM [lib://Real Total Gross Domestic Product by State for West Virginia]

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

Best,

Sunny

Re: Merging data from multiple API connections & Adding new column

jleefjcapital — Thu, 21 May 2015 14:49:21 GMT

It worked! I just had to add a couple of brackets around "State". Thank you!

Re: Merging data from multiple API connections & Adding new column

sunny_talwar — Thu, 21 May 2015 14:51:07 GMT

Awesome

Glad I was able to help

Best,

Sunny