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

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

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

Capture.PNG

jleefjcapital
Creator II
Creator II
Author

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

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

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?