Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm very new to QlikView and and looking for some help with my data load script.
The data has columns:
ID e.g.6082
UserID, e.g 3200
Username, e.g. Smith, Victoria
Role, e.g. Developer
Overall Status, e.g. 0
Learning IDs e.g. 13;16;18; (However this could be any number of variable split using a ';' as the delimiter)
LearningCompDts e.g. IP;31 Dec 2011;IP; (This always has the same amount of parameters as the Learning IDs field)
NB - The Learning IDs and LearningCompDts fields can be null.
I have worked out how to split the Learning IDs and the LearningCompDts down so that there is a single value in each column for each row. This is the code used:
LOAD ID,
Userid,
Username,
subfield([Learning IDs],';',IterNo()) as Column1,
subfield([Learning CompDts],';',IterNo()) as Column2
FROM
(ooxml, embedded labels, table is [LTW Record])
While (IterNo()<=substringcount([Learning IDs],';')+1)
AND
(len(subfield([Learning IDs],';',IterNo()))+len(subfield([Learning CompDts],';',IterNo()))>0);
I've though about creating the table holding the ID, UserID, Username, LearningID and LearningCompDt and then once this has loaded splitting the values out however I have know Idea how to do this and I'm really stuck?
Any help is much appreciated.
Thanks you in advance
Victoria
This works however, there are some rows for which the LearningID and LearningCompDts are null.
What I want to do is, before splitting up the cell data, change the value to 'NA;' for those cells which are null. This is so when the cell data is split all null values are recorded as NA;.
I have got the code to replace the null values:
However, when I add the code to split the data up the NA values are discarded. Code is:
LOAD ID,
Userid,
Username,
If(IsNull([Learning IDs]),'NA;',[Learning IDs]) AS LearningID,
If(IsNull([Learning CompDts]),'NA;',[Learning CompDts]) AS LearningCompDt,
subfield([Learning IDs],';',IterNo()) as Column1,
subfield([Learning CompDts],';',IterNo()) as Column2
FROM
(ooxml, embedded labels, table is [LTW Record])
While (IterNo()<=substringcount([Learning IDs],';')+1)
AND
(len(subfield([Learning IDs],';',IterNo()))+len(subfield([Learning CompDts],';',IterNo()))>0);
LOAD ID,
Userid,
Username,
If(IsNull([Learning IDs]),'NA;',[Learning IDs]) AS LearningID,
If(IsNull([Learning CompDts]),'NA;',[Learning CompDts]) AS LearningCompDt,
FROM
(ooxml, embedded labels, table is [LTW Record]);
Hi Jason,
Thanks for your help, the code didn't seem to work but I have found a way around the issue.
I start by loading all the non null values and then concatenate the null values as extra rows to the previously created table:
InternalReq:
LOAD
UserID,Username,
[Learning IDs] AS REC_LearningIDs,
[Learning CompDts] AS REC_LearningCompDts,
subfield([Learning IDs],';',IterNo()) as REC_LearningID,
subfield([Learning CompDts],';',IterNo()) as REC_LearningCompDt
FROM
(ooxml, embedded labels, table is [LTW Record])
WHILE (IterNo()<=substringcount([Learning IDs],';')+1)
AND
(len(subfield([Learning IDs],';',IterNo()))+len(subfield([Learning CompDts],';',IterNo()))>0);CONCATENATE(InternalReq)
LOAD
UserID,Username,
[Learning IDs] AS REC_LearningIDs,
[Learning CompDts] AS REC_LearningCompDts,
If(IsNull([Learning IDs]),'NA',[Learning IDs]) AS REC_LearningID,
If(IsNull([Learning CompDts]),'NA',[Learning CompDts]) AS REC_LearningCompDt
FROM
(ooxml, embedded labels, table is [LTW Record])
WHERE(IsNull([Learning IDs]));
This works a treat and is much simpler.
Thank you for your help though
Victoria
I think you started on the right lines by using SubField() but you're over-complicating it a bit (although it's not completely clear what you're trying to achieve).
Try this in your script:
//Create map for replacing NULL values
Map_Null:
MAPPING LOAD
Null(),
'NA'
AutoGenerate 1;
//This bit tells all NULL values in these fields should be replaced using the above map (actioned just before the data is finally stored)
MAP "Learning ID","Learning CompDts" USING Map_Null;
//Load your data, breaking up the delimited string as you go. Without the third parameter in SubField() QV will create a new row for each value
Data:
LOAD UserID,
Username,
SubField([Learning IDs],';') AS [Learning ID],
SubField([Learning CompDts],';') AS [Learning CompDts]
FROM
<your data>
(ooxml, embedded labels, table is Sheet1);
Hope this helps (examples attached).
Jason
Hi Jason,
Thanks for the help but that isn't working.
I've just realised that my first post got reordered somehow so I'll try and re explain what I am trying to do.
Here's an example of the data that I have:
UserID | Username | Learning ID | Header 6 |
---|---|---|---|
1 | 123 | 12;15;167; | IP;IP;IP |
2 | 456 | ||
3 | 789 | 13;26; | 31 Dec 2011;IP |
4 | 234 | 14; | 15 Jan 2012; |
5 | 567 | 17;47;24;58 | 12 Jan 2012;IP;18 Sep 2011;2 Oct 2011; |
So the code I have:
LOAD
UserID,
Username,
subfield([Learning IDs],';',IterNo()) as REC_LearningID,
subfield([Learning CompDts],';',IterNo()) as REC_LearningCompDts
FROM
(ooxml, embedded labels, table is [Sheet1])
WHILE (IterNo()<=substringcount([Learning IDs],';')+1)
AND
(len(subfield([Learning IDs],';',IterNo()))+len(subfield([Learning CompDts],';',IterNo()))>0);
Provides me with the data:
UserID | Username | Learning ID | LearningCompDt |
---|---|---|---|
1 | 123 | 12 | IP |
1 | 123 | 15 | IP |
1 | 123 | 167 | IP |
3 | 789 | 13 | 31 Dec 2011 |
3 | 789 | 26 | IP |
4 | 234 | 14 | 15 Jan 2012 |
5 | 567 | 17 | 12 Jan 2012 |
5 | 567 | 47 | IP |
5 | 567 | 24 | 18 Sep 2011 |
5 | 567 | 58 | 2 Oct 2011 |
However, as you can see UserID 2 has disappeared.
What I want the data to look like is:
UserID | Username | Learning ID | LearningCompDt |
---|---|---|---|
1 | 123 | 12 | IP |
1 | 123 | 15 | IP |
1 | 123 | 167 | IP |
2 | 456 | NA | NA |
3 | 789 | 13 | 31 Dec 2011 |
3 | 789 | 26 | IP |
4 | 234 | 14 | 15 Jan 2012 |
5 | 567 | 17 | 12 Jan 2012 |
5 | 567 | 47 | IP |
5 | 567 | 24 | 18 Sep 2011 |
5 | 567 | 58 | 2 Oct 2011 |
Using the code provided above I'm still getting the data displayed in the first output table above i.e. without any NA values. This is the exact code that I have input:
Map_Null:
MAPPING LOAD
Null(),
'NA;'
AutoGenerate 1;Map "Learning ID","Learning CompDts" USING Map_Null;
InternalReq:
LOADUserID,
Username,
subfield([Learning IDs],';',IterNo()) as REC_LearningID,
subfield([Learning CompDts],';',IterNo()) as REC_LearningCompDts
FROM
(ooxml, embedded labels, table is [LTW Record])
WHILE (IterNo()<=substringcount([Learning IDs],';')+1)
AND
(len(subfield([Learning IDs],';',IterNo()))+len(subfield([Learning CompDts],';',IterNo()))>0);
Have I put the code in the wrong place?
Any help on this is much appreciated.
Thanks
Victoria
A quick update.
The mapping code works but only when the substring code is not used.
Is there a way to load the table and then apply the substring code?
Thanks
Vicki
Hi Vicki,
Your issue is with the >0 here:
AND (len(subfield([Learning IDs],';',IterNo()))+len(subfield([Learning CompDts],';',IterNo()))>0)
To get round this you need to load it once with >0 then load again with a natural join, this time with =0. See below.
Hope this helps - seems to work.
Jason
//Create map for replacing NULL values
SET NULLINTERPRET =<Null()>;
Map_Null:
MAPPING LOAD * INLINE [
Find,Replace
<Null()>,NA
,NA
];
Map "REC_LearningID","REC_LearningCompDts" USING Map_Null;
InternalReq:
LOAD
UserID,
Username,
subfield([Learning IDs],';',IterNo()) as REC_LearningID,
subfield([Learning CompDts],';',IterNo()) as REC_LearningCompDts
FROM
\\eurfiler6home.fm.rbsgrp.net\michaej\MyGEOSProfile\FDR\Desktop\SubField_Data.xlsx
(ooxml, embedded labels, table is Sheet1)
WHILE (IterNo()<=substringcount([Learning IDs],';')+1)
AND (len(subfield([Learning IDs],';',IterNo()))+len(subfield([Learning CompDts],';',IterNo()))>0)
;
JOIN (InternalReq)
LOAD
UserID,
Username,
subfield([Learning IDs],';',IterNo()) as REC_LearningID,
subfield([Learning CompDts],';',IterNo()) as REC_LearningCompDts
FROM
\\eurfiler6home.fm.rbsgrp.net\michaej\MyGEOSProfile\FDR\Desktop\SubField_Data.xlsx
(ooxml, embedded labels, table is Sheet1)
WHILE (IterNo()<=substringcount([Learning IDs],';')+1)
AND (len(subfield([Learning IDs],';',IterNo()))+len(subfield([Learning CompDts],';',IterNo()))=0)
;
Hi Jason,
Thanks for your help, the code didn't seem to work but I have found a way around the issue.
I start by loading all the non null values and then concatenate the null values as extra rows to the previously created table:
InternalReq:
LOAD
UserID,Username,
[Learning IDs] AS REC_LearningIDs,
[Learning CompDts] AS REC_LearningCompDts,
subfield([Learning IDs],';',IterNo()) as REC_LearningID,
subfield([Learning CompDts],';',IterNo()) as REC_LearningCompDt
FROM
(ooxml, embedded labels, table is [LTW Record])
WHILE (IterNo()<=substringcount([Learning IDs],';')+1)
AND
(len(subfield([Learning IDs],';',IterNo()))+len(subfield([Learning CompDts],';',IterNo()))>0);CONCATENATE(InternalReq)
LOAD
UserID,Username,
[Learning IDs] AS REC_LearningIDs,
[Learning CompDts] AS REC_LearningCompDts,
If(IsNull([Learning IDs]),'NA',[Learning IDs]) AS REC_LearningID,
If(IsNull([Learning CompDts]),'NA',[Learning CompDts]) AS REC_LearningCompDt
FROM
(ooxml, embedded labels, table is [LTW Record])
WHERE(IsNull([Learning IDs]));
This works a treat and is much simpler.
Thank you for your help though
Victoria
Glad you got it working although there's no need for the IF statements as your WHERE clause takes care of it...
Jason