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]);
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.
Try one of these:
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.
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!
Looks like your DATE field doesn't contain date values yet. Can you post a small qlik sense app and some example source data?
Gysbert, I think the OP might have resolved this issue here:
Date field is being loaded, but when I add the WHERE statement it doesn't load-
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.
The problem is, the program runs, but it doesn't load (the lower case).
It doesn't even load with just the single Where statement specifying the seriesID.
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.