9 Replies Latest reply: Nov 8, 2011 6:22 AM by Sandra Jehoel RSS

    Splitting multicoded fields

      I am looking for a way to split multicoded fields. See an example in the table below. For example, for ID2, we need to enter a value of 1 and avalue of 2.

       

       

      uniqueid

       

      value

       

      ID1

       

      1

       

      ID2

       

      1;2

       

      ID3

       

      1;2;3

       

      ID4

       

      2;3

       

      ID5

       

      3

       

       

       

      I have found two possible solutions, but neither of theseare right for our purposes.

       

      Solution 1: Subfield

       

      Load uniqueid,
          
      subfield(value,';') as ValueSubfield

       

      This indeed splits the value field correctly. However, italso adds rows. For example, there is now a row of data for ID2 value=1 and arow for ID2 value=2. This then causes problems for our expressions, because ofduplicate rows for uniqueids. These duplicate rows cause problems for averages,counts etc. It is possible to compensate for this in the expressions, by usingaggr functions, but this is not ideal and is likely to lead to errors at somepoint.

       

       

      Solution 2: Excel manipulation and Cross table

       

      For the second solution, we manipulated the raw data inExcel, to create separate fields for each value (value1, value2 and value3 intable below). We then create a crosstable in the QlikView script. This solutiongives us the correct data structure. However, it is very time consuming, Ourdata files contain many multicoded fields, and we need a quick and easy way todeal with these, without having to manipulate data outside of QlikView. Perhapsthere is a way of doing this more easily within the QlikView script?

       

       

      uniqueid

       

      value

       

      value1

       

      value2

       

      value3

       

      ID1

       

      1

       

      1

       

       

       

       

       

      ID2

       

      1;2

       

      1

       

      2

       

       

       

      ID3

       

      1;2;3

       

      1

       

      2

       

      3

       

      ID4

       

      2;3

       

       

       

      2

       

      3

       

      ID5

       

      3

       

       

       

       

       

      3

       

       

       

      crosstable (row, ValueCross)

      Load uniqueid,

      value1,

      value2,

      value3

       

       

      How can I split a multicoded field? Is there perhaps a way of using subfield but creating a crosstable (rather than duplicate rows)?

        • Splitting multicoded fields
          Matthew Crowther

          If your situation is as simplistic as your example (which I doubt) then the following works for me:

           

          Data:

          LOAD uniqueid,

               value,

               SubField(value,';',1) as Sub_Value_1,

               SubField(value,';',2) as Sub_Value_2,

               SubField(value,';',3) as Sub_Value_3,

               SubField(value,';',4) as Sub_Value_4

          FROM

          C:\SubField.xls

          (biff, embedded labels, table is Sheet1$);

           

          Obviously this wouldn't work too well if you have hundreds of values - in which case I'd use the same principal in conjunction with a loop based on a count of the max number of ';' in the value field.

           

          Hope that helps,

           

          Matt - Visual Analytics Ltd

            • Re: Splitting multicoded fields

              Hi Matt,

               

              Thanks for the suggestions, but your solution does not give us what we need.

               

              It produces the following:

               

              uniqueid

               

              value

               

              Sub_Value_1

               

              Sub_Value_2

               

              Sub_Value_3

               

              Sub_Value_4

               

              ID1

               

              1

               

              1

               

               

               

               

               

               

               

              ID2

               

              1;2

               

              1

               

              2

               

               

               

               

               

              ID3

               

              1;2;3

               

              1

               

              2

               

              3

               

               

               

              ID4

               

              2;3

               

              2

               

              3

               

               

               

               

               

              ID5

               

              3

               

              3

               

               

               

               

               

               

               

               

               

               

              Hpwever, what we are looking for is this. For this example, I split the value field in Excel, and then created a cross table in QlikView. This avoids the creation of duplicate rows (tghe duplicate rows you see in this table do not actually exist in the data). However, this olution is too labour intensive, because we have many projects with many multicoded fields.

               

              In this case, you can select the value '2', and all IDs with that value become selected. You don;t have that option in the solution you suggested.

               

              uniqueid

               

              value

               

              ValueCross

               

              ID1

               

              1

               

              1

               

              ID2

               

              1;2

               

              1

               

              ID2

               

              1;2

               

              2

               

              ID3

               

              1;2;3

               

              1

               

              ID3

               

              1;2;3

               

              2

               

              ID3

               

              1;2;3

               

              3

               

              ID4

               

              2;3

               

              2

               

              ID4

               

              2;3

               

              3

               

              ID5

               

              3

               

              3

               

               

               

              To clarify what we're trying to do, I'll give you some example context. The multicoded field is a question in a market research survey. Respondents could tick all that apply. For example, "which of the following websites did you visit today? 1) Google 2) Amazon 3) Ebay". In our QlikView tool we then need to be able to select all respondents who, for example, visited Amazon.

                • Re: Splitting multicoded fields
                  Dominic Mander

                  If your data is as simple as 1;2;3 or 1;3 etc etc then this will do what you need I think ... it will load each entry into two fields, once as is, and once with 'Field_' appended as the field name. You can then use a Generic Load (which is effectively the opposite of a crosstable) to re-assemble the data into the format you need.

                   

                  TestData:

                  LOAD * INLINE [

                      UniqueID, Value

                      ID1, 1

                      ID2, 1;2

                      ID3, 1;2;3

                      ID4, 2;3

                      ID5, 1;3

                      ID6, 1;2;3;4

                      ID7, 3;4

                      ID8, 4

                  ];

                   

                  Data:

                  LOAD

                      UniqueID

                  RESIDENT TestData;

                   

                  Generic:

                  GENERIC

                  LOAD

                      UniqueID,

                      'Field_' & Value AS Field,

                      Value

                  ;

                  LOAD

                      UniqueID,

                      Subfield(Value, ';') AS Value

                  RESIDENT TestData;

                   

                  FOR i = 0 to NoOfTables()

                    TableList:

                    LOAD '[' & TableName($(i)) & ']' as Tablename AUTOGENERATE 1

                    WHERE WildMatch(TableName($(i)), 'Generic.*');

                  NEXT i

                   

                  FOR i = 1 to FieldValueCount('Tablename')

                    LET vTable = FieldValue('Tablename', $(i));

                    LEFT JOIN (Data) LOAD * RESIDENT $(vTable);

                    DROP TABLE $(vTable);

                  NEXT i

                   

                  DROP TABLE TableList;

                   

                  DROP TABLE TestData;

                   

                  ... I can attach a sample QVW shortly if you need

                   

                  ... I've updated the example above to include the sample data I was testing with in an inline load so you should be able to copy it directly into a new document and see what it's doing.

                   

                  Message was edited by: DominicMander

                    • Re: Splitting multicoded fields
                      Dominic Mander

                      Incidently ... you could replace the piece of script that loads the table I named "Generic" with the following script ...

                       

                      Generic:

                      GENERIC

                      LOAD

                          UniqueID,

                          'Field_' & Subfield(Value, ';') AS Field,

                          1 AS Value

                      RESIDENT TestData;

                       

                      ... to give yourself a 1 or null flag in each field rather than the actual value. You might find this easier in your front end, depending on what you're wanting to do with the data.

                        • Re: Splitting multicoded fields

                          Dominic, it looks like your solution brings us closer to the solution, but I am still not sure how to get all the values into a single field.

                           

                          This is what I get: I will still need to combine the 'Field' fields into a single field. When I tried to crosstable these fields, I got the problem of duplicate rows again.

                           

                          uniqueid

                           

                          Field_1

                           

                          Field_2

                           

                          Field_3

                           

                          ID1

                           

                          1

                           

                           

                           

                           

                           

                          ID2

                           

                          1

                           

                          2

                           

                           

                           

                          ID3

                           

                          1

                           

                          2

                           

                          3

                           

                          ID4

                           

                           

                           

                          2

                           

                          3

                           

                          ID5

                           

                           

                           

                           

                           

                          3

                           
                            • Re: Splitting multicoded fields
                              Anita Fuchten

                              could do something like this (if the subfield amount is fixed max?)

                               

                              TEST:

                              LOAD Unique ID

                                        , Field_1

                              RESIDENT Generic;

                               

                              CONCATENATE (TEST)

                              LOAD UniqueID

                                       , Field_2

                              RESIDENT Generic;

                               

                              ... etc

                               

                               

                              if the subfields should be flexible ... try doing this with some kind of loop?

                              • Re: Splitting multicoded fields
                                Dominic Mander

                                Sorry, I'm not sure I've understood what you want the data to look like when it's finished ... the table you've posted as an example of what you get is what I thought you wanted, but I've obviously missunderstood. Doing a Generic load and then a Crosstable is a little bit like going round in a circle back to where you started.

                                 

                                Do you just want to be able to select any record that has 1 in it, or any record that has 2 in it or any record that has 3 in it?

                                 

                                In which case you don't need any of the above, Generic or Crosstable ... you just do the following.

                                 

                                TestData:

                                LOAD * INLINE [

                                    UniqueID, Value

                                    ID1, 1

                                    ID2, 1;2

                                    ID3, 1;2;3

                                    ID4, 2;3

                                    ID5, 3

                                ];

                                 

                                Select:

                                LOAD * INLINE [

                                    Value, Select

                                    1, 1

                                    1;2, 1

                                    1;3, 1

                                    1;2;3, 1

                                    2, 2

                                    1;2, 2

                                    2;3, 2

                                    1;2;3, 2

                                    3, 3

                                    1;3, 3

                                    2;3, 3

                                    1;2;3, 3

                                ];

                                 

                                Selecting 1, 2 or 3 in the Select filed will filter the UniqueID field to any entries that have what you selected in the Value field ... is this what you need?

                                  • Re: Splitting multicoded fields
                                    John Witherspoon

                                    Logically speaking, you can either have one row per subvalue or one column per subvalue.  Those are your only options.  The extra subvalues have to go SOMEWHERE. 

                                     

                                    However, duplicate rows shouldn't cause problems as long as you put the ID vs. subvalue connection in a SECOND table.  Don't duplicate OTHER data.  So if you have this:

                                     

                                    ID  value otherfield
                                    ID1 1     hello
                                    ID2 1;2   goodbye

                                     

                                    You want this:

                                     

                                    ID  value
                                    ID1 1
                                    ID2 1
                                    ID2 2

                                     

                                    ID  otherfield
                                    ID1 hello
                                    ID2 goodbye

                                     

                                    You WILL need to do count(distinct ID) instead of count(ID), but most everything else should work fine without changes.  Let me know if you need help transforming the first data structure to the second, but all the clues are already in your initial attempt and in the thread.

                                      • Re: Splitting multicoded fields

                                        Thanks John, I am astonished the answer was that simple all along!

                                         

                                        I am now using Subfield to split the multicode fields, and put these values in a separate table.

                                         

                                        The averages across other fields (in a separate table) continue to work properly. And we can live with having to use Distinct for the uniqueid field.