topic Re: Where statement in Qlik Sense in App Development

Where statement in Qlik Sense

jleefjcapital — Mon, 24 Oct 2016 21:03:47 GMT

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

Re: Where statement in Qlik Sense

Gysbert_Wassenaar — Tue, 25 Oct 2016 09:13:42 GMT

Try one of these:

Where [seriesID] = 'SMS01000000000000001'
Where Match([seriesID], 'SMS01000000000000001')

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,

Not sure what you want to do. Perhaps Date(MakeDate(Year, Mid(period,2)),'YYYY-MM-DD') as DATE.

Re: Where statement in Qlik Sense

jleefjcapital — Tue, 25 Oct 2016 16:49:01 GMT

Thank you! Good to see you again!

It loaded, but I'm not able to use the data yet. I think I need to create a master calendar which I tried to do using this code below, but I get an error message saying this field is not found (error message pasted above code). The field that's not found is ' <= ' . It's worked previously so I'm not sure what I'm doing wrong.

Thank you, again!

Re: Where statement in Qlik Sense

Gysbert_Wassenaar — Wed, 26 Oct 2016 06:36:27 GMT

Looks like your DATE field doesn't contain date values yet. Can you post a small qlik sense app and some example source data?

Re: Where statement in Qlik Sense

sunny_talwar — Wed, 26 Oct 2016 10:19:40 GMT

Gysbert, I think the OP might have resolved this issue here:

Master Calendar Interval () step error‌

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 16:44:09 GMT

Date field is being loaded, but when I add the WHERE statement it doesn't load-

Re: Where statement in Qlik Sense

Gysbert_Wassenaar — Wed, 26 Oct 2016 16:50:34 GMT

If you use more than one condition in the where and both should evaluate to true then you need to add and "AND" between them. Just like you did in the lower screenshot.

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 16:52:36 GMT

The problem is, the program runs, but it doesn't load (the lower case).

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 16:53:08 GMT

It doesn't even load with just the single Where statement specifying the seriesID.

Re: Where statement in Qlik Sense

Gysbert_Wassenaar — Wed, 26 Oct 2016 16:59:07 GMT

Then either __FK_data contains only null values or the SMS01000000000000001 value doesn't exist in seriesID. Make sure to use the exact case sensitive value. There's a lot of 0's in that value. Perhaps you've used on too many or not enough.

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 17:10:06 GMT

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.

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 17:11:27 GMT

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

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 17:13:13 GMT

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?

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 17:16:39 GMT

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

Re: Where statement in Qlik Sense

Gysbert_Wassenaar — Wed, 26 Oct 2016 17:19:09 GMT

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

....

;

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 17:24:18 GMT

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.

Re: Where statement in Qlik Sense

Gysbert_Wassenaar — Wed, 26 Oct 2016 17:27:11 GMT

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.

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 17:32:37 GMT

Does this look right?

Re: Where statement in Qlik Sense

jleefjcapital — Wed, 26 Oct 2016 17:33:21 GMT

This is the error message I get:

Re: Where statement in Qlik Sense

Gysbert_Wassenaar — Wed, 26 Oct 2016 17:37:28 GMT

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

....

;