44 Replies Latest reply: Oct 27, 2016 9:01 AM by Sunny Talwar RSS

    if statement to change field name

    Jes Lee

      I'm using this IF statement to change the field name on condition of another field value.

       

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

       

      So basically, I am looking to create another field called [Alabama] using data points from [value] when [seriesID] = 'SMS01000000000000001'.

       

      This code does not work in its current form.

        • Re: if statement to change field name
          Sunny Talwar

          Can you share screenshots of a table which include seriesID, value and Alabama?

            • Re: if statement to change field name
              Jes Lee

              I'm providing a screenshot of Date, SeriesID, and Value.  I was not able to rename the Value field to Alabama. 

                    • Re: if statement to change field name
                      Sunny Talwar

                      You will need to create a table instead of individual filters. I want to see how they look in a single row.

                        • Re: if statement to change field name
                          Jes Lee

                          Here you go.  I think one issue is, the data are monthly, and the dates are showing up as daily so there are many nulls.

                           

                            • Re: if statement to change field name
                              Sunny Talwar

                              So my question to you is.... If you now create this field called Alabama

                               

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

                               

                              If won't show value for these rows?

                              Capture.PNG

                                • Re: if statement to change field name
                                  Jes Lee

                                  Yes.  It creates a separate field, but there's no data associated with it. 

                                    • Re: if statement to change field name
                                      Sunny Talwar

                                      Can you add the image of the fourth column added to your table? Also can you share the complete script where you are doing this?

                                        • Re: if statement to change field name
                                          Jes Lee

                                          Here's the table -

                                            • Re: if statement to change field name
                                              Sunny Talwar

                                              These seems like two different fields.

                                              1) series_id (in your chart)

                                              2) seriesID (in your if statement)

                                               

                                              Capture.PNG

                                               

                                              Not sure which one is right, but try using series_id in the if statement and see if that helps

                                                • Re: if statement to change field name
                                                  Jes Lee

                                                  I fixed the code to be consistent with the table, but when I do this series_id is null for all values.

                                                   

                                                   

                                                    • Re: if statement to change field name
                                                      Sunny Talwar

                                                      That is because your data doesn't align properly with the series_id. Once you are able to align it, your problems should be resolved

                                                        • Re: if statement to change field name
                                                          Jes Lee

                                                          It's strange, because when I use the single series API, I don't have this problem.  It's only when I try to pull multiple series, I run into issues with the dates, and alignment with series_id.

                                                           

                                                          Is this a database issue, or something I can fix via code? 

                                                            • Re: if statement to change field name
                                                              Sunny Talwar

                                                              I have no idea, I would need to look at your complete script and preferably in a text format and not an image

                                                                • Re: if statement to change field name
                                                                  Jes Lee

                                                                  LIB CONNECT TO 'Total Nonfarm, All Employees, Alabama Alaska and Arizona, Thousands, SA';

                                                                  RestConnectorMasterTable:
                                                                  SQL SELECT
                                                                  "status",
                                                                  "responseTime",
                                                                  "__KEY_root",
                                                                  (SELECT
                                                                    "@Value",
                                                                    "__FK_message"
                                                                  FROM "message" FK "__FK_message" ArrayValueAlias "@Value"),
                                                                  (SELECT
                                                                    "__FK_Results",
                                                                    "__KEY_Results",
                                                                    (SELECT
                                                                     "seriesID",
                                                                     "__FK_series",
                                                                     "__KEY_series",
                                                                     (SELECT
                                                                      "series_title",
                                                                      "series_id",
                                                                      "seasonality",
                                                                      "survey_name",
                                                                      "measure_data_type",
                                                                      "commerce_industry",
                                                                      "commerce_sector",
                                                                      "area",
                                                                      "__FK_catalog"
                                                                     FROM "catalog" FK "__FK_catalog"),
                                                                     (SELECT
                                                                      "year",
                                                                      "period",
                                                                      "periodName",
                                                                      "value",
                                                                      "__FK_data",
                                                                      "__KEY_data",
                                                                      (SELECT
                                                                       "@Value" AS "@Value_u0",
                                                                       "__FK_footnotes"
                                                                      FROM "footnotes" FK "__FK_footnotes" ArrayValueAlias "@Value_u0"),
                                                                      (SELECT
                                                                       "__FK_calculations",
                                                                       "__KEY_calculations",
                                                                       (SELECT
                                                                        "1",
                                                                        "3",
                                                                        "6",
                                                                        "12",
                                                                        "__FK_net_changes"
                                                                       FROM "net_changes" FK "__FK_net_changes"),
                                                                       (SELECT
                                                                        "1" AS "1_u0",
                                                                        "3" AS "3_u0",
                                                                        "6" AS "6_u0",
                                                                        "12" AS "12_u0",
                                                                        "__FK_pct_changes"
                                                                       FROM "pct_changes" FK "__FK_pct_changes")
                                                                      FROM "calculations" PK "__KEY_calculations" FK "__FK_calculations")
                                                                     FROM "data" PK "__KEY_data" FK "__FK_data")
                                                                    FROM "series" PK "__KEY_series" FK "__FK_series")
                                                                  FROM "Results" PK "__KEY_Results" FK "__FK_Results")
                                                                  FROM JSON (wrap on) "root" PK "__KEY_root";

                                                                  [message]:
                                                                  LOAD [@Value] AS [@Value]
                                                                  RESIDENT RestConnectorMasterTable
                                                                  WHERE NOT IsNull([__FK_message]);


                                                                  [catalog]:
                                                                  LOAD [series_title] AS [series_title],
                                                                  [series_id] AS [series_id],
                                                                  [seasonality] AS [seasonality],
                                                                  [survey_name] AS [survey_name],
                                                                  [measure_data_type] AS [measure_data_type],
                                                                  [commerce_industry] AS [commerce_industry],
                                                                  [commerce_sector] AS [commerce_sector],
                                                                  [area] AS [area]
                                                                  RESIDENT RestConnectorMasterTable
                                                                  WHERE NOT IsNull([__FK_catalog]);


                                                                  // [footnotes]:
                                                                  // LOAD [@Value_u0] AS [@Value_u0]
                                                                  // RESIDENT RestConnectorMasterTable
                                                                  // WHERE NOT IsNull([__FK_footnotes]);


                                                                  // [net_changes]:
                                                                  // LOAD [1] AS [1],
                                                                  //  [3] AS [3],
                                                                  //  [6] AS [6],
                                                                  //  [12] AS [12]
                                                                  // RESIDENT RestConnectorMasterTable
                                                                  // WHERE NOT IsNull([__FK_net_changes]);


                                                                  // [pct_changes]:
                                                                  // LOAD [1_u0] AS [1_u0],
                                                                  //  [3_u0] AS [3_u0],
                                                                  //  [6_u0] AS [6_u0],
                                                                  //  [12_u0] AS [12_u0]
                                                                  // RESIDENT RestConnectorMasterTable
                                                                  // WHERE NOT IsNull([__FK_pct_changes]);

                                                                  // mapIDName:
                                                                  // MAPPING LOAD * INLINE [
                                                                  // ID, VALUE
                                                                  // 'SMS01000000000000001', 'Alabama'
                                                                  // 'SMS02000000000000001', 'Alaska'
                                                                  // 'SMS04000000000000001', 'Arizona'

                                                                  // ];

                                                                  [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,
                                                                      [series_id] AS [series_id],
                                                                      [value] AS [value],
                                                                      If([series_id] = 'SMS01000000000000001',[value]) as [Alabama]
                                                                  //     If(seriesID = 'SMS02000000000000001', [value]) as [Alaska],
                                                                  //     If(seriesID = 'SMS04000000000000001', [value]) as [Arizona]
                                                                  //     ApplyMap('mapIDName', [seriesID], 'Not mapped') as [value] 

                                                                  RESIDENT RestConnectorMasterTable
                                                                  WHERE NOT IsNull([__FK_data]) ;

                                                                  [series]:
                                                                  LOAD [seriesID] AS [seriesID]
                                                                  RESIDENT RestConnectorMasterTable
                                                                  WHERE NOT IsNull([__FK_series]);

                                                                  [root]:
                                                                  LOAD [status] AS [status],
                                                                  [responseTime] AS [responseTime]
                                                                  RESIDENT RestConnectorMasterTable
                                                                  WHERE NOT IsNull([__KEY_root]);

                                                                  // [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 Alabama_nonfarm
                                                                  // RESIDENT RestConnectorMasterTable
                                                                  // WHERE NOT IsNull([__FK_data]) and [seriesID] = 'SMS01000000000000001';


                                                                  DROP TABLE RestConnectorMasterTable;

                                    • Re: if statement to change field name
                                      Sunny Talwar

                                      When you just run this, do you see series_id and value align? If it doesn't then the issue is in the rest connectors load. I have never done this, so won't really know what is going wrong. But lets just try this and see what we get

                                       

                                      LIB CONNECT TO 'Total Nonfarm, All Employees, Alabama Alaska and Arizona, Thousands, SA';

                                      RestConnectorMasterTable:
                                      SQL SELECT
                                      "status",
                                      "responseTime",
                                      "__KEY_root",
                                      (SELECT
                                        "@Value",
                                        "__FK_message"
                                      FROM "message" FK "__FK_message" ArrayValueAlias "@Value"),
                                      (SELECT
                                        "__FK_Results",
                                        "__KEY_Results",
                                        (SELECT
                                         "seriesID",
                                         "__FK_series",
                                         "__KEY_series",
                                         (SELECT
                                          "series_title",
                                          "series_id",
                                          "seasonality",
                                          "survey_name",
                                          "measure_data_type",
                                          "commerce_industry",
                                          "commerce_sector",
                                          "area",
                                          "__FK_catalog"
                                         FROM "catalog" FK "__FK_catalog"),
                                         (SELECT
                                          "year",
                                          "period",
                                          "periodName",
                                          "value",
                                          "__FK_data",
                                          "__KEY_data",
                                          (SELECT
                                           "@Value" AS "@Value_u0",
                                           "__FK_footnotes"
                                          FROM "footnotes" FK "__FK_footnotes" ArrayValueAlias "@Value_u0"),
                                          (SELECT
                                           "__FK_calculations",
                                           "__KEY_calculations",
                                           (SELECT
                                            "1",
                                            "3",
                                            "6",
                                            "12",
                                            "__FK_net_changes"
                                           FROM "net_changes" FK "__FK_net_changes"),
                                           (SELECT
                                            "1" AS "1_u0",
                                            "3" AS "3_u0",
                                            "6" AS "6_u0",
                                            "12" AS "12_u0",
                                            "__FK_pct_changes"
                                           FROM "pct_changes" FK "__FK_pct_changes")
                                          FROM "calculations" PK "__KEY_calculations" FK "__FK_calculations")
                                         FROM "data" PK "__KEY_data" FK "__FK_data")
                                        FROM "series" PK "__KEY_series" FK "__FK_series")
                                      FROM "Results" PK "__KEY_Results" FK "__FK_Results")
                                      FROM JSON (wrap on) "root" PK "__KEY_root";

                                        • Re: if statement to change field name
                                          Jes Lee

                                          Yes, the seriesID and values do align.   Should I just copy and paste into editor? 

                                          • Re: if statement to change field name
                                            Jes Lee

                                            Sunny, I just ran the response, and series_id and value do not align.  So, I think you're right.  It may be the rest connection.  Is that a problem on the Qlik side or data provider?

                                             

                                              • Re: if statement to change field name
                                                Sunny Talwar

                                                I am not sure and I don't know how can help resolve this for you. May be get in touch with Qlik and see if they can help you find the right person.

                                                  • Re: if statement to change field name
                                                    Jes Lee

                                                    Ok, thank you. 

                                                    • Re: if statement to change field name
                                                      Jes Lee

                                                      It's aligned now.  You gave me a good clue.   I changed one small detail in the REST connector-- so that Key Generation Strategy is set to sequence ID.

                                                       

                                                      I think this was all that was needed to join the different tables by Sequence ID.

                                                       

                                                       

                                                      However, now I'm trying to figure out the If statement to parse out data by State (using Sequence ID).  Here's an example of my IF statement - do you see anything wrong with this?

                                                       

                                                       

                                                      [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([series_id] = '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]);

                                                        • Re: if statement to change field name
                                                          Sunny Talwar

                                                          I don't know what to look for? Are you getting unexpected results? Where?

                                                            • Re: if statement to change field name
                                                              Jes Lee

                                                              The if statements in the middle of the load statement -

                                                               

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

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

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

                                                               

                                                              I am looking to create three new fields by series ID called Alabama, Alaska, and Arizona.

                                                                • Re: if statement to change field name
                                                                  Jes Lee

                                                                  Fields are being created with the right names, but no data are associated with them. 

                                                                    • Re: if statement to change field name
                                                                      Sunny Talwar

                                                                      It comes down to the same question again, do the series_id align with value? And I see you are again using seriesID? What is difference between series_id and seriesID?

                                                                        • Re: if statement to change field name
                                                                          Jes Lee

                                                                          They seem to be used interchangeably.  Yes, the fields align now after I added the key generation specification. 

                                                                            • Re: if statement to change field name
                                                                              Sunny Talwar

                                                                              So can you create a table with seriesID (NOT series_id), value, Alabama, Alaska, Arizona?

                                                                                  • Re: if statement to change field name
                                                                                    Sunny Talwar

                                                                                    Are you serious? I asked you to add seriesID and you added series_id. Please be consistent in your script and front end, else we will never solve this

                                                                                     

                                                                                    Capture.PNG

                                                                                        • Re: if statement to change field name
                                                                                          Sunny Talwar

                                                                                          Now it makes a lot of sense, can you try these:

                                                                                           

                                                                                          If(WildMatch([seriesID], 'SMS01*'),[value]) as [Alabama],

                                                                                          If(WildMatch([seriesID], 'SMS02*'), [value]) as [Alaska],

                                                                                          If(WildMatch([seriesID], 'SMS04*'), [value]) as [Arizona],

                                                                                            • Re: if statement to change field name
                                                                                              Jes Lee

                                                                                              It's still not loading.....

                                                                                               

                                                                                               

                                                                                               

                                                                                              [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(WildMatch([seriesID], 'SMS01*'),[value]) as [Alabama],

                                                                                                  If(WildMatch([seriesID], 'SMS02*'), [value]) as [Alaska],

                                                                                                  If(WildMatch([seriesID], 'SMS04*'), [value]) as [Arizona],

                                                                                              [__KEY_data] AS [__KEY_data],

                                                                                              [__FK_data] AS [__KEY_series]

                                                                                              RESIDENT RestConnectorMasterTable

                                                                                              WHERE NOT IsNull([__FK_data]);

                                                                                                • Re: if statement to change field name
                                                                                                  Sunny Talwar

                                                                                                  Not really sure what might be causing this, can you share the whole script again? Everything after the RestConnector script. I don't really need to see that part since we already know that it is working now

                                                                                                    • Re: if statement to change field name
                                                                                                      Jes Lee

                                                                                                      Here you go.  This is everything past the RestConnector script.

                                                                                                       

                                                                                                       

                                                                                                      [message]:
                                                                                                      LOAD [@Value] AS [@Value],
                                                                                                      [__FK_message] AS [__KEY_root]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__FK_message]);


                                                                                                      [catalog]:
                                                                                                      LOAD [series_title] AS [series_title],
                                                                                                      [series_id] AS [series_id],
                                                                                                      [seasonality] AS [seasonality],
                                                                                                      [survey_name] AS [survey_name],
                                                                                                      [measure_data_type] AS [measure_data_type],
                                                                                                      [commerce_industry] AS [commerce_industry],
                                                                                                      [commerce_sector] AS [commerce_sector],
                                                                                                      [area] AS [area],
                                                                                                      [__FK_catalog] AS [__KEY_series]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__FK_catalog]);


                                                                                                      [footnotes]:
                                                                                                      LOAD [@Value_u0] AS [@Value_u0],
                                                                                                      [__FK_footnotes] AS [__KEY_data]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__FK_footnotes]);


                                                                                                      [net_changes]:
                                                                                                      LOAD [1] AS [1],
                                                                                                      [3] AS [3],
                                                                                                      [6] AS [6],
                                                                                                      [12] AS [12],
                                                                                                      [__FK_net_changes] AS [__KEY_calculations]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__FK_net_changes]);


                                                                                                      [pct_changes]:
                                                                                                      LOAD [1_u0] AS [1_u0],
                                                                                                      [3_u0] AS [3_u0],
                                                                                                      [6_u0] AS [6_u0],
                                                                                                      [12_u0] AS [12_u0],
                                                                                                      [__FK_pct_changes] AS [__KEY_calculations]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__FK_pct_changes]);


                                                                                                      [calculations]:
                                                                                                      LOAD [__KEY_calculations] AS [__KEY_calculations],
                                                                                                      [__FK_calculations] AS [__KEY_data]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__FK_calculations]);


                                                                                                      [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(WildMatch([seriesID], 'SMS01*'),[value]) as [Alabama],

                                                                                                          If(WildMatch([seriesID], 'SMS02*'), [value]) as [Alaska],

                                                                                                          If(WildMatch([seriesID], 'SMS04*'), [value]) as [Arizona],


                                                                                                      [__KEY_data] AS [__KEY_data],
                                                                                                      [__FK_data] AS [__KEY_series]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__FK_data]);


                                                                                                      [series]:
                                                                                                      LOAD [seriesID] AS [seriesID],
                                                                                                      [__KEY_series] AS [__KEY_series],
                                                                                                      [__FK_series] AS [__KEY_Results]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__FK_series]);


                                                                                                      [Results]:
                                                                                                      LOAD [__KEY_Results] AS [__KEY_Results],
                                                                                                      [__FK_Results] AS [__KEY_root]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__FK_Results]);


                                                                                                      [root]:
                                                                                                      LOAD [status] AS [status],
                                                                                                      [responseTime] AS [responseTime],
                                                                                                      [__KEY_root] AS [__KEY_root]
                                                                                                      RESIDENT RestConnectorMasterTable
                                                                                                      WHERE NOT IsNull([__KEY_root]);


                                                                                                      DROP TABLE RestConnectorMasterTable;