7 Replies Latest reply: Nov 28, 2011 1:10 PM by John Witherspoon RSS

    Additional field in crosstable load

    Joe Kirwan

      Here is my crosstable load:

       

      Crosstable (DCode, Data,2)

      LOAD Type,

                PCode,

                [1],

                [2],

                [3],

                [4],

      ......

                [12]

       

      FROM etc.etc

       

      Now I want to load a column which combines the columns Type, PCode and DCode as NEWCOL.

       

      So after [12], I have a new line

       

      Type&PCode&DCode as NEWCOL

       

      But when I try to do that, I get a message:

       

      Field Not Found - <DCode>

       

      Ideas anyone?

       

       

       

       

        • Re: Additional field in crosstable load
          John Witherspoon

          DCode doesn't exist until you finish the crosstable load, because you're building DCode in the crosstable load.  So you can't refer to it in the load, because it doesn't exist yet.  If I've understood what you want, you could left join after the crosstable load. 

           

          LEFT JOIN (Whatever)
          LOAD DISTINCT
          Type
          ,PCode
          ,DCode
          ,Type&PCode&DCode as NEWCOL
          RESIDENT Whatever
          ;

            • Additional field in crosstable load
              Joe Kirwan

              Hi John

               

              I get what you are saying about DCode.

               

              But I haven't used RESIDENT before. Would you mind explaining how it works?

               

              Many thanks

               

              Joe

                • Re: Additional field in crosstable load
                  John Witherspoon

                  RESIDENT loads from a table that you're already loaded into memory.

                   

                  Data:

                  LOAD * INLINE [

                  Key, Value1

                  A, 50

                  B, 100

                  ];

                  LEFT JOIN (Data)

                  LOAD

                  Key

                  ,Value1 + 10 as Value2

                  RESIDENT Data

                  ;

                   

                  Will give you this table:

                   

                  Data:

                  Key, Value1, Value2

                  A, 50, 60

                  B, 100, 110

                   

                  I'm sure there's a lot more information in the documentation.  In the specific example above, you'd be better off doing the calculation in the initial load.  In many other examples, you're better off doing the calculation in a preceding load.  But in some cases, such as a crosstable load, neither of those options is available, and the left join resident is a reasonable approach.

                    • Additional field in crosstable load
                      Joe Kirwan

                      Hi John

                       

                      Thanks for the clarification - It allows me to better understand the solution proposed in your first response.

                       

                      Would you believe that there is NO reference to the RESIDENT in the tutorial. The manual does refer to it, but only by way of providing syntax, and not by explaining its function.

                       

                      This is frustrating for a non technical person like myself.

                       

                      Thats why the community is so great.

                       

                      Joe

                       

                      I will test your solution in the morning, when I assume I wll be able to mark it correct.

                        • Re: Additional field in crosstable load
                          John Witherspoon

                          Wow.  Yeah, all I could find with a quick glance through the help file was this:

                           

                          resident is used if data should be loaded from a previously loaded input table.

                           

                          That hardly seems like adequate documentation, particularly for something so very fundamental.  I use resident loads constantly.  It tends to be better for performance when you can avoid them, but you can't always avoid them.  I can almost never avoid them.

                            • Additional field in crosstable load
                              Joe Kirwan

                              Hi John

                               

                              If you had a moment, coudl you possibly explain how DISTINCT works in the above?

                               

                              I had another issue with LEFT JOIN here

                               

                              http://community.qlik.com/message/167004#167004

                               

                              In fact I think I resolved this by using DISTINCT - but I have no idea why!

                               

                              Rgds

                               

                              Joe

                                • Re: Additional field in crosstable load
                                  John Witherspoon

                                  Let's say your data looks like this.

                                   

                                  Type, PCode, DCode
                                  A, B, C
                                  A, B, C
                                  D, E, F

                                   

                                  If you don't use distinct, each of the two ABC rows will join to each of the two ABC rows, thus creating four ABC rows:

                                   

                                  Type, PCode, DCode, NEWCOL
                                  A, B, C, ABC
                                  A, B, C, ABC
                                  A, B, C, ABC
                                  A, B, C, ABC
                                  D, E, F, DEF

                                   

                                  This sort of error is often difficult to spot, because QlikView only shows you unique combinations of fields when you display data, so those four rows would look like a single row most of the time.

                                   

                                  If you can guarantee that values won't repeat like this, you could eliminate the distinct, which would probably speed up the script slightly.