3 Replies Latest reply: Jun 19, 2012 11:30 AM by Bradley Coyne RSS

    Split data from single column

    Alan Hendrickx

      Hi All,

       

      I am being provided with .csv documents which contain columns with composite information.

      There are two fields that I need to work with, the first contains the date and time combined, e.g. 17/03/2011  21:55:00.

      I need to be able to extract the date and work with that. Later, I would like to be able to work with the time field also.

      This one is my top priority.

       

      The second is a field containing two types of services, separated by a semi-colon e.g. Video; Audio.

      I need to be able to split this info out also.

       

      I am using the Personal Edition so unfortunately I can't view any qlikview docs!! I have attached an excel document with a sample record (highlighted in yellow) to demonstrate what I am working with.

       

      I expect the best place to do this is in the script, however I have been unable to crack this to date.

       

      Many thanks,

      Alan

        • Split data from single column

          Hi Portolad,

           

          While loading data from CSV use Subfield function to split date and time. You can also convert date loaded in string format from CSV to date format using Date# function and Time# function. Use the below script for reference:

           

          LOAD Id,

               Name,

               Type,

               LeadSource,

               OwnerId,

               Date#(subfield(CreatedDate,' ',1),'MM/DD/YYYY') as CreatedDate,

               Time#(subfield(CreatedDate,' ',2),'hh:mm') as CreatedTime,

               CreatedById,

               LastModifiedDate,

               LastModifiedById,

               SystemModstamp,

               LastStageChangeDate,

               FiscalYear,

               FiscalQuarter,

               subfield(Opp_Service_Type_925,';',1) as Opp_Service_Type1,

               subfield(Opp_Service_Type_925,';',2) as Opp_Service_Type2

          FROM

          [Opportunity_QV example.csv]

          (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

           

          Also take the below things into consideration:

           

          subfield(S, ';' ,1) returns NULL if S is an empty string

          subfield(S, ';' ,1) returns an empty string if S is ';'

           

          I believe that in Qlikview 10 or earlier version the reference doc is avalible under C:\Programs Files\Qlikivew\Doc folder.

            • Split data from single column

              Hi Anosh,

               

              your suggestion works well for one occurrence in the table. Thanks!

              What would you suggest for the following:

               

              Product 1, Category  1
              Product 1, Category 2
              Product 2, Catergory 1
              Product 2, Category 2
              Product 3, Category 1
              Product 4, Category 1

               

               

              Here, I cannot save the Product or the Category under the same title in the same table... yet, I only need to end up with 4 individual products in Category 1 and 2 individual products in Category 2.

               

              Assumption: I cannot change the data at the source (needs to be done in QV)

               

              Can you think of any suggestions for the script and for the load separately?

               

              Thanks,

              Imi

                • Re: Split data from single column
                  Bradley Coyne

                  here is a simple scenario using an excel flatfile.

                  You wish to split this field into two seperate fields 1. CreatedDate, 2.CreatedTime for reporting purposes in qlikview.

                   

                  time field.png

                   

                  Code snippet

                   

                   

                  Directory;
                  LOAD time,
                  Date#(subfield(time,' ',1),'MM/DD/YYYY') as CreatedDate,
                  Time#(subfield(time,' ',2),'hh:mm') as CreatedTime

                  FROM

                   

                  [in this case it is loaded from an excel sheet].