Merging data from multiple API connections & Adding new column

[jleefjcapital]

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

[sunny_talwar]

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

[jleefjcapital]

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.

[jonathandienst]

value as [REAL GDP]

should read

value as [REAL GDP],

(trailing comma)

[sunny_talwar]

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

[jleefjcapital]

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

[sunny_talwar]

Awesome

Glad I was able to help

Best,

Sunny