Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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].