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