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

Merging data from multiple API connections & Adding new column

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

6 Replies
sunny_talwar
Not applicable

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

Goal is to get a table like this??

DateStateReal 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
Not applicable

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

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.

jontydkpi
Not applicable

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

value as [REAL GDP]

should read

value as [REAL GDP],

(trailing comma)

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

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

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
Not applicable

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

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

sunny_talwar
Not applicable

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

Awesome

Glad I was able to help

Best,

Sunny