Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create three new fields from a field named [value] parsed by a unique identifier called [series_id]. I am using a RESTConnector to pull the data from an official source (BLS.gov). I am pulling multiple series at once, which I hope to parse out within Qlik to use as separate dimension in the visualization tool.
The three new fields will be named [Alabama], [Alaska], and [Arizona]. In this process, I hope to populate data from [value] into each of these new fields using [series_id].
I've attached my QVF. Here is the Load step that includes the IF statement.
[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([seriesID] = '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]);
!Hi Jessica tackled things a different way.
1. Created a master dimension item call state using
If([series_id]='SMS01000000000000001','Alabama',
If([series_id]='SMS02000000000000001','Alaska',
If([series_id]= 'SMS04000000000000001','Arizona')))
Then pivoted on the data summing on Value.
See attached
Thank you. I think there was something wrong with the Rest connector. I am using a web connection instead and it's working now.
Didn't try to reload just fixed what was there using a different approach.
Seems quite a static way of trying to do things storing values in the model that way.
Anyway glad its now working for you.
Regards
Andy
Hi Andy,
I came back to download the attached qvf file, but it's no longer available. Would you be able to repost?
I would like to replicate the nested IF statements for something else that I'm working on. Would you be able to help me?
Sure ill reload it for you in the morning
Thank you!
There you go.
Andy, I don't see the file. I was told files don't appear all the time. I'm fine for now, but I wish there was a more robust way to share files through these discussions.
Replace
If([seriesID] = 'SMS01000000000000001',[value]) as [Alabama],
If([seriesID]='SMS02000000000000001', [value]) as [Alaska],
If([seriesID]= 'SMS04000000000000001', [value]) as [Arizona],
with
If([series_id]='SMS01000000000000001','Alabama',
If([series_id]='SMS02000000000000001','Alaska',
If([series_id]= 'SMS04000000000000001','Arizona'))) as State,