Where statement in Qlik Sense

[jleefjcapital]

After loading data using a REST connector, I am trying to parse it using another load step and where statement as follows.

[seriesID] is comprised of three different series codes.  I would like to given them each a separate name using this load step.

Next,  I would like to format the date which is currently stored as two separate columns -- year for year and period for month (M01, M02, etc) into the following format -- YYYY-MM-DD.  I have been using this code to do this, but I'm not sure how to go about doing it with two separate date columns...Is there a concatenate function?   Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY') AS DATE,

To rename and parse out the series by ID:

[series]:
LOAD [seriesID] AS [seriesID]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_series]);

Employment:
Load
"[seriesID] as "Alabama_employment"
Resident [series];
Where [seriesID] in SMS01000000000000001;

To reformat the date:

[data]:

LOAD [year] AS [year],

[period] AS [period],

[periodName] AS [periodName],

[value] AS [value]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_data]);

[jleefjcapital]

I've verified that the codes are identical.  I've actually copied and pasted from same source.  Data loads for all series id once I remove the seriesid Where statement so I have verified that data exists for the series.

[jleefjcapital]

With the where statement, the value field is empty too. 

[jleefjcapital]

Actually, what I'm trying to do is assign a unique name for "value" for each series id.   Can I do this another way without having to use a where statement?

[jleefjcapital]

Something like this where I place the Where statement within the data step.   Value as 'Alabama' where [seriesID] = ' '

[Gysbert_Wassenaar]

Ok, try this:

if(Match(SeriesID, 'SMS01......etc'), 'Alabama') as Value

Or if you have several matches to make create a small mapping table and use the applymap function:

mapIDName:

MAPPING LOAD * INLINE [

ID, Value

SMS01, Abc

SMS02, Cde

...etc

];

Table

LOAD

     ...some fields...

     ApplyMap('mapIDName', SeriesID, 'Not mapped') as Value,

     ...some other fields...

FROM

     ...

     ;

At least I think you want to get the value 'Alabama' in the field value, not create a field named 'Alabama' which contains the value from the field name value. Or is that what you're trying to do? If it is then you need three lines to create and fill three fields:

LOAD

     ...some fields...,

     If(SeriesID = 'SMS01....etc', value) as Alabama,

     If(SeriesID = 'SMS02....etc', value) as Kansas,

     If(SeriesID = 'SMS04....etc', value) as Florida,

     ....other fields....

FROM

     ....

     ;

[jleefjcapital]

Here's a screenshot of the main field values.  Dataset contains data values for three series ids by date.   I would like to assign distinct names for the Value field based on seriesID.

[Gysbert_Wassenaar]

No offense, but you don't really seem to know what you're doing. Please consider following a training. You're wasting a lot of your time now. A two day investment will really pay itself back very quickly.

[jleefjcapital]

Does this look right?

[jleefjcapital]

This is the error message I get:

[Gysbert_Wassenaar]

Yes, but rename mapIDName to mapIDname. It's important to use the exact case sensitive names.

But it will put the values 'Alabama', 'Alaska' and 'Arizona' in the field value. If that's what you're after it will do the trick. But if you're trying to create three new fields named 'Alabama', 'Alaska' and 'Arizona' and fill these with the values from the field [value] then you need separate statement for each new field.

LOAD

     ...some fields...

     If(SeriesID = 'SMS0100000000000001', value) as [Alabama],

     If(SeriesID = 'SMS0200000000000001', value) as [Alaska],

     If(SeriesID = 'SMS0400000000000001', value) as [Arizona],

     ...other fields....

RESIDENT

     ....

     ;