10 Replies Latest reply: Nov 3, 2016 6:14 AM by Andy Weir RSS

    If statement to create a separate field by unique identifier

    Jes Lee

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