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: 
Not applicable

Issues with data loading. Please help

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

6 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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:

UserIDUsernameLearning IDHeader 6
112312;15;167;IP;IP;IP
2456
378913;26;31 Dec 2011;IP
423414;15 Jan 2012;
556717;47;24;5812 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:

UserIDUsernameLearning IDLearningCompDt
112312IP
112315IP
1123167IP
37891331 Dec 2011
378926IP
42341415 Jan 2012
55671712 Jan 2012
556747IP
55672418 Sep 2011
5567582 Oct 2011


However, as you can see UserID 2 has disappeared.

What I want the data to look like is:

UserIDUsernameLearning IDLearningCompDt
112312IP
112315IP
1123167IP
2456NANA
37891331 Dec 2011
378926IP
42341415 Jan 2012
55671712 Jan 2012
556747IP
55672418 Sep 2011
5567582 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

Not applicable
Author

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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)

;

Not applicable
Author

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Jason