Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jleefjcapital
Contributor

If statement to create a separate field by unique identifier

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]);

Tags (1)
10 Replies
ogster1974
Honored Contributor II

Re: If statement to create a separate field by unique identifier

!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
Contributor

Re: If statement to create a separate field by unique identifier

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
Honored Contributor II

Re: If statement to create a separate field by unique identifier

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
Contributor

Re: If statement to create a separate field by unique identifier

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
Honored Contributor II

Re: If statement to create a separate field by unique identifier

Sure ill reload it for you in the morning

jleefjcapital
Contributor

Re: If statement to create a separate field by unique identifier

Thank you!

ogster1974
Honored Contributor II

Re: If statement to create a separate field by unique identifier

There you go.

jleefjcapital
Contributor

Re: If statement to create a separate field by unique identifier

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
Valued Contributor

Re: If statement to create a separate field by unique identifier

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,

Community Browser