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]

Yes, the seriesID and values do align.   Should I just copy and paste into editor? 

[jleefjcapital]

Sunny, I just ran the response, and series_id and value do not align.  So, I think you're right.  It may be the rest connection.  Is that a problem on the Qlik side or data provider?

[sunny_talwar]

So when you create a table, they do align? Not like this?

[sunny_talwar]

I am not sure and I don't know how can help resolve this for you. May be get in touch with Qlik and see if they can help you find the right person.

[jleefjcapital]

Ok, thank you. 

[jleefjcapital]

It's aligned now.  You gave me a good clue.   I changed one small detail in the REST connector-- so that Key Generation Strategy is set to sequence ID.

I think this was all that was needed to join the different tables by Sequence ID.

However, now I'm trying to figure out the If statement to parse out data by State (using Sequence ID).  Here's an example of my IF statement - do you see anything wrong with this?

[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([series_id] = 'SMS01000000000000001',[value]) as [Alabama],

    If(seriesID = 'SMS02000000000000001', [value]) as [Alaska],

    If(seriesID = 'SMS04000000000000001', [value]) as [Arizona],

[__KEY_data] AS [__KEY_data],

[__FK_data] AS [__KEY_series]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_data]);

[sunny_talwar]

I don't know what to look for? Are you getting unexpected results? Where?

[jleefjcapital]

The if statements in the middle of the load statement -

    If([seriesID] = 'SMS01000000000000001',[value]) as [Alabama],

    If(seriesID = 'SMS02000000000000001', [value]) as [Alaska],

    If(seriesID = 'SMS04000000000000001', [value]) as [Arizona],

I am looking to create three new fields by series ID called Alabama, Alaska, and Arizona.

[jleefjcapital]

Fields are being created with the right names, but no data are associated with them. 

[sunny_talwar]

It comes down to the same question again, do the series_id align with value? And I see you are again using seriesID? What is difference between series_id and seriesID?