6 Replies Latest reply: Feb 9, 2012 6:24 AM by Jason Michaelides RSS

Issues with data loading. Please help

victoria.smith2

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
[C:\Users\Smi1ac\Documents\QlikView\LTW Record.xlsx]
(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
[C:\Users\Smi1ac\Documents\QlikView\LTW Record.xlsx]
(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
[C:\Users\Smi1ac\Documents\QlikView\LTW Record.xlsx]
(ooxml, embedded labels, table is [LTW Record]);

  • Re: Issues with data loading. Please help
    Jason Michaelides

    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
      victoria.smith2

      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
      [C:\Users\Smi1ac\Documents\QlikView\Book1.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);
      
      


      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
      [C:\Users\Smi1ac\Documents\QlikView\LTW Record.xlsx]
      (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
      victoria.smith2

      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

        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
          victoria.smith2

          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
          [C:\Users\Smi1ac\Documents\QlikView\LTW Record.xlsx]
          (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
          [C:\Users\Smi1ac\Documents\QlikView\LTW Record.xlsx]
          (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