Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
jleefjcapital
Contributor II

Where statement in Qlik Sense

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Where statement in Qlik Sense

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.


talk is cheap, supply exceeds demand
31 Replies
MVP & Luminary
MVP & Luminary

Re: Where statement in Qlik Sense

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.


talk is cheap, supply exceeds demand
jleefjcapital
Contributor II

Re: Where statement in Qlik Sense

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!

MVP & Luminary
MVP & Luminary

Re: Where statement in Qlik Sense

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


talk is cheap, supply exceeds demand

Re: Where statement in Qlik Sense

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

Master Calendar Interval () step error

jleefjcapital
Contributor II

Re: Where statement in Qlik Sense

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

MVP & Luminary
MVP & Luminary

Re: Where statement in Qlik Sense

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.


talk is cheap, supply exceeds demand
jleefjcapital
Contributor II

Re: Where statement in Qlik Sense

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

jleefjcapital
Contributor II

Re: Where statement in Qlik Sense

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

MVP & Luminary
MVP & Luminary

Re: Where statement in Qlik Sense

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.


talk is cheap, supply exceeds demand