Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

How to split entire file on two delimiters?

Any help is appreciated.

I have a .dat file, with sections separated by ETX and SOH Ascii characters. I can split it by one or the other in Qlikview, but I need it by both. Is there a way to do this, or a way to replace all ETX with SOH in the entire file, and then I can delimit on just SOH?

The data in the document looks like this, and extends down for several rows.

orig_source[ETX]SO[SOH]header_created_by[ETX]Joe[SOH]header_created_date[ETX]06/02/17[SOH]

1 Solution

Accepted Solutions
daveamz01
Contributor III

Re: How to split entire file on two delimiters?

Hi Aaron,

Have a look at the attached file. I've used SubField and preceding load.

I'm not sure if this is the best solution for your data set, but I hope it will give you at least an idea of how this can be tackled.

Regards,

David

View solution in original post

6 Replies

Re: How to split entire file on two delimiters?

Are you loading this from a text file?

Not applicable

Re: How to split entire file on two delimiters?

A .dat file

In my example code, the [ETX] is where an actual ETX character is

daveamz01
Contributor III

Re: How to split entire file on two delimiters?

Hi Aaron,

Have a look at the attached file. I've used SubField and preceding load.

I'm not sure if this is the best solution for your data set, but I hope it will give you at least an idea of how this can be tackled.

Regards,

David

View solution in original post

Not applicable

Re: How to split entire file on two delimiters?

With this solution, would I have to do this for each row or column?

The actual data I'm working with extends on for much longer and deeper.

daveamz01
Contributor III

Re: How to split entire file on two delimiters?

I'm sure that the real data set is much more complex and requires more tweaks.

In the attach there is an example of how the previous solution can be further extended. I've duplicate the rows, but I suppose in your .dat file, the rows are not separated by [CR][LF] special chars.

If the .dat structure is too complex I suggest you to look for an external tool (like a power shell script, python script etc.) to convert it to csv or some other readable format for qlik.

DAT files are pretty generic and there is no out of the box tool to suit every .dat file. You can read more here: https://www.lifewire.com/what-is-a-dat-file-2620550

Regards,

David

Not applicable

Re: How to split entire file on two delimiters?

Thank you for the extensive response. I got it figured it out now, thanks.