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]

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

[sunny_talwar]

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

[jleefjcapital]

[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

[jleefjcapital]

[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],

[jleefjcapital]

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]);

[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

[jleefjcapital]

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;

[sunny_talwar]

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?