if statement to change field name

[jleefjcapital]

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.

[jleefjcapital]

Here's the table -

[sunny_talwar]

These seems like two different fields.

1) series_id (in your chart)

2) seriesID (in your if statement)

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

[jleefjcapital]

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

[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

[jleefjcapital]

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? 

[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

[jleefjcapital]

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;

[sunny_talwar]

Leaving for home, will check this out in a little bit

[jleefjcapital]

Ok, thanks, Sunny.

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