Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
They seem to be used interchangeably. Yes, the fields align now after I added the key generation specification.
So can you create a table with seriesID (NOT series_id), value, Alabama, Alaska, Arizona?
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
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],
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]);
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
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;
Can you add this to the script:
[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],
WildMatch([seriesID], 'SMS01*') as Check1,
WildMatch([seriesID], 'SMS02*') as Check2,
WildMatch([seriesID], 'SMS04*') as Check4,
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]);
Do you see -1 for Check1, Check2 and Check4 anywhere when you add it to the table above?