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

    if statement to change field name

    Jessica 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
              Jessica 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
                          Jessica 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
                                  Jessica 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
                                          Jessica 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
                                                  Jessica 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
                                                          Jessica 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
                                                                  Jessica 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";