Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jleefjcapital
Creator II
Creator II

if statement to change field name

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.

44 Replies
jleefjcapital
Creator II
Creator II
Author

Here's the table -

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

jleefjcapital
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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