topic Re: How to successfully merging delimited data and columns in QlikView

How to successfully merging delimited data and columns

Wed, 26 Mar 2014 14:00:42 GMT

I am a new Qlik user. Please assist me with merging delimited data and columns. I have been able to separate the delimited data by using the SubField function. I then have taken the columns and changed the names to one name. The problem is that it does not appear to be finding all the data in each column. It is finding the majority of the data in each column, but not all.

Re: How to successfully merging delimited data and columns

Anonymous — Wed, 26 Mar 2014 14:08:05 GMT

hi Richard,

if you are trying to load a delimited file,

1. in the script page (Ctrl+E), click on Table Files button.

2. Make sure "Files of Type" is All Table Files and then select your csv file.

3. In the upcoming window, choose Delimited in File Type (top left) if it hasn't been automatically detected.

4. In the Delimiter dropdown, select the type of delimiter that is used, example - comma, colon, tab, etc. you can also select custom (last option in the drop down) to select custom delimiters such as pipes, etc. Below is a screenshot for reference.

5. Select Header Size and Labels as Embedded Labels.

6. Make sure your table looks right in preview.

7. Follow the wizard to load. The columns should all correctly.

if this is not what you are looking for, may be a sample data file and example output will help!

Good luck!

Re: How to successfully merging delimited data and columns

Wed, 26 Mar 2014 14:22:56 GMT

Thank you. You have provided me with insight into Qlik. However, it appears I have 2 delimiters. One is a comma and one is a pipe. If I select pipe, it merges all data into one row. I need the columns and to be able to search the delimited data inside of one of the columns. What do you advise?

Re: How to successfully merging delimited data and columns

Anonymous — Wed, 26 Mar 2014 14:36:11 GMT

ok...could you post a sample file so I can take a look and figure out the best approach?

Re: How to successfully merging delimited data and columns

Wed, 26 Mar 2014 15:10:45 GMT

Here is a sample of what I am doing right now to separate ICD9 codes. However, as mentioned above, it appears to not be 100% accurate.

LOAD [Unique Identifier],

     [Date of Service],

     Date(Date#( [Date of Service],'MMDDYYYY')) as Servicedate,

     Year(Date(Date#( [Date of Service],'MMDDYYYY'))) as Year,

     Month(Date(Date#( [Date of Service],'MMDDYYYY'))) as Month,

     [ICD9 Code 1] as ICD9_1,

     [ICD9 Code 2] as ICD9_2,

     [ICD9 Code 3] as ICD9_3,

     [ICD9 Code 4] as ICD9_4,

     [ICD9 Code 5] as ICD9_5,

     [CPT Procedure Code] as CPT_1,

     [HCPCS Code] as CPT_2,

     [Admission Type]

FROM

[..x);

Claims:

LOAD [Unique Identifier],

     [ICD 9],

     SubField ([ICD 9],'|',1) as ICD9_1,

     SubField ([ICD 9],'|',2) as ICD9_2,

     SubField ([ICD 9],'|',3) as ICD9_3,

     SubField ([ICD 9],'|',4) as ICD9_4,

     SubField ([ICD 9],'|',5) as ICD9_5,


     [CPT/HCPCS], ...

LOAD [Unique Identifier],

[ICD9_1] as ICD9

RESIDENT Claims;

ICD9:

LOAD [Unique Identifier],

[ICD9_2] as ICD9

RESIDENT Claims;...