Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]);
I don't think that was appropriate nor professional. Do you represent Qlik?
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.
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.
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?
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
I just uploaded the QVF using the Upload to Share link. Please let me know if you'd received it.
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.
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
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!
Weird. I can see it:
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
2. Create a connection to the file
3. Change the settings so the fields will be loaded correctly
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.