If statement to create a separate field by unique identifier

[jleefjcapital]

I am trying to create three new fields from a field named [value] parsed by a unique identifier called [series_id].    I am using a RESTConnector to pull the data from an official source (BLS.gov).  I am pulling multiple series at once, which I hope to parse out within Qlik to use as separate dimension in the visualization tool.

The three new fields will be named [Alabama], [Alaska], and [Arizona].   In this process, I hope to populate data from [value] into each of these new fields using [series_id].

I've attached my QVF.  Here is the Load step that includes the IF statement.

[data]:

LOAD [year] AS [year],

[period] AS [period],

Date(Makedate([year],right([period],2)), 'MMMYYYY') as MONTHYEAR,

    Date(Makedate([year],right([period],2)),'M/D/YYYY') as DATE,

[value] AS [value],

   If([seriesID] = 'SMS01000000000000001',[value]) as [Alabama],

    If([seriesID]='SMS02000000000000001', [value]) as [Alaska],

    If([seriesID]= 'SMS04000000000000001', [value]) as [Arizona],

[__KEY_data] AS [__KEY_data],

[__FK_data] AS [__KEY_series]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_data]);

[ogster1974]

!Hi Jessica tackled things a different way.

1. Created a master dimension item call state using

If([series_id]='SMS01000000000000001','Alabama',

    If([series_id]='SMS02000000000000001','Alaska',

    If([series_id]= 'SMS04000000000000001','Arizona')))

Then pivoted on the data summing on Value.

See attached

[jleefjcapital]

Thank you.  I think there was something wrong with the Rest connector.  I am using a web connection instead and it's working now. 

[ogster1974]

Didn't try to reload just fixed what was there using a different approach.

Seems quite a static way of trying to do things storing values in the model that way.

Anyway glad its now working for you.

Regards

Andy

[jleefjcapital]

Hi Andy,

I came back to download the attached qvf file, but it's no longer available.  Would you be able to repost?

I would like to replicate the nested IF statements for something else that I'm working on.  Would you be able to help me?  

[ogster1974]

Sure ill reload it for you in the morning

[jleefjcapital]

Thank you!

[ogster1974]

There you go.

[jleefjcapital]

Andy, I don't see the file.  I was told files don't appear all the time.  I'm fine for now, but I wish there was a more robust way to share files through these discussions.

[rupamjyotidas]

Replace

  If([seriesID] = 'SMS01000000000000001',[value]) as [Alabama],

    If([seriesID]='SMS02000000000000001', [value]) as [Alaska],

    If([seriesID]= 'SMS04000000000000001', [value]) as [Arizona],

with

If([series_id]='SMS01000000000000001','Alabama',

    If([series_id]='SMS02000000000000001','Alaska',

    If([series_id]= 'SMS04000000000000001','Arizona'))) as State,