Split data from single column

[#1]

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

[#2]

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.

[#3]

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

[#4]

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.

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].