Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
value as [REAL GDP]
should read
value as [REAL GDP],
(trailing comma)
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
It worked! I just had to add a couple of brackets around "State". Thank you!
Awesome
Glad I was able to help
Best,
Sunny