topic Split data from single column in QlikView

Split data from single column

Sat, 21 Jan 2012 10:48:39 GMT

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

Sat, 21 Jan 2012 11:52:48 GMT

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

Sat, 11 Feb 2012 13:19:37 GMT

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

Tue, 19 Jun 2012 15:30:32 GMT

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