topic Re: Issues with data loading. Please help in QlikView

Issues with data loading. Please help

Wed, 08 Feb 2012 15:36:24 GMT

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

Re: Issues with data loading. Please help

Jason_Michaelides — Wed, 08 Feb 2012 16:18:24 GMT

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

Re: Issues with data loading. Please help

Thu, 09 Feb 2012 10:15:54 GMT

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:
LOAD 
     UserID,
     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

Issues with data loading. Please help

Thu, 09 Feb 2012 10:25:01 GMT

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

Re: Issues with data loading. Please help

Jason_Michaelides — Thu, 09 Feb 2012 10:41:57 GMT

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

Re: Issues with data loading. Please help

Thu, 09 Feb 2012 11:19:42 GMT

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

Re: Issues with data loading. Please help

Jason_Michaelides — Thu, 09 Feb 2012 11:24:21 GMT

Glad you got it working although there's no need for the IF statements as your WHERE clause takes care of it...

Jason