Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jleefjcapital
Creator II
Creator 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]);

31 Replies
jleefjcapital
Creator II
Creator II
Author

I don't think that was appropriate nor professional.  Do you represent Qlik?

Gysbert_Wassenaar

Like I said, no offense. I'm just trying to give sincerely meant advice. I think you could do with some training. You will be more productive in shorter time. My apologies if you feel I stepped out of line. I don't represent Qlik. Though as a Qlik Community MVP I do in a way represent this user community. So if you feel I'm not behaving correctly toward you, you should certainly call me out on that. Once again my apologies. No criticism was intended.

I do try to help out people here. And that includes pointing them to resources that might help them Training is one of those resources. No worries, I won't pester you about it any longer.


talk is cheap, supply exceeds demand
jleefjcapital
Creator II
Creator II
Author

Yes, I'm looking to do the latter.   I'd like to create three separate fields for each series ID, named Alabama, Alaska, and Arizona, and fill them with the values.   I used the if statements to create three separate fields, but it didn't work. The [value] field has been loaded with data, but state fields were not.  Here is my code:

Regarding training, yes I'm new to Qlik.  I was told this forum/community was created to help those like myself.  When Qlik was marketed to me and our company, we were told it was extremely user friendly with no required training.  It would be great if you could help me through this issue.  We may consider training after this issue is resolved. 

Gysbert_Wassenaar

‌I'm afraid Marketing hasn't been completely honest or perhaps just not complete. End users won't need much if any training. Just a short instruction will do. But what you're doing is developing a data model. That's rather more complicated than using the end result.

I don't see what's going wrong. The code looks ok and if the field [value] is filled correctly then I don't see why the three state fields don't get filled if the seriesID values are correct. I'd have to look at the data. Can you post a small example data set and a small qlik sense app with the script code?


talk is cheap, supply exceeds demand
jleefjcapital
Creator II
Creator II
Author

Hi Gysbert,

I'm trying to send you the qvf, but how should I attach it?   For the data, here's the link -- http://download.bls.gov/pub/time.series/sm/sm.data.1.Alabama

Thank you,

Jes

jleefjcapital
Creator II
Creator II
Author

I just uploaded the QVF using the Upload to Share link.   Please let me know if you'd received it.  

Gysbert_Wassenaar

I don't see it. But the community software has a small bug that sometimes hides an attachement. The Upload to Share link doesn't ring a bell. If you reply from the discussion instead of from your Inbox you can change to the Advanced Editor. There you can find a Attach link at the bottom right of the editor. You can use that to select a file an attach it to the discussion.

I've downloaded some of the data from download.bls.gov - /pub/time.series/sm/ and created a small qlik sense app. I've attached it here. I've created three state fields and filled those with the values from the [value] field. That works ok here. But if you want to do that for all the states you'll have to create a lot of extra fields. That may not be the best way to do things. It can make creating charts that compare states more complicated since you can't then use state as dimension like I did in the line chart. You'll need expressions for each separate state.

Anyway, I hope this helps you fix the issue.


talk is cheap, supply exceeds demand
jleefjcapital
Creator II
Creator II
Author

Yes, I would like to create states to use as dimensions to compare them.   I don't see your attachment.  I will repost the question in another discussion and attach my files.

Thank you again!  It's great to hear it's working on your end.   As of today, I was able to get the value field aligned with the series id, which may have created part of the issue.  In any case, I will post now.

Thank you!

Jes

jleefjcapital
Creator II
Creator II
Author

Gysbert,

I just reposted my question in a separate discussion window along with the QVF attachment.  I've tagged  you as well...not sure if you'll be notified, but it's called "If statement to create a separate field by unique identifier "

Thank you again!

Gysbert_Wassenaar

Weird. I can see it:

c237497.png

Anyway, I think the REST connector is causing the issue. Since the data files are available online by http you can use the Web connector to create connections to the data files on download.bls.gov:

1. Select the Web file connector

c237497_2.png

2. Create a connection to the file

c237497_3.png

3. Change the settings so the fields will be loaded correctly

c237497_4.png

Alternatively you can also download the files like I did in the example and work with file connections to the local files. You will then of course occasionally need to download the updates files.


talk is cheap, supply exceeds demand