topic Exporting Qlikview sheet using double piple in QlikView

Exporting Qlikview sheet using double piple

Thu, 10 Oct 2013 18:23:57 GMT

Hi,

Been trying to find for ways to export some data to a txt file using double pipe '||' but it appears that the following statement only takes the first character and export with single pipe. Is there any way more than one characters can be used as delimiter?

STORE COMPANY INTO 'COMPANY.txt' (txt, delimiter is '||');

Thanks,

John

Re: Exporting Qlikview sheet using double piple

rustyfishbones — Thu, 10 Oct 2013 18:42:36 GMT

can you provide a sample app

Re: Exporting Qlikview sheet using double piple

Thu, 10 Oct 2013 19:25:10 GMT

Hi,

This is the script.

Thanks,

John

COMPANY:

load

[Essbase Member Name]

, Description

, [CO_To Currency Code]

, [CO_Default Country Num]

, '' AS [SET_OF_BOOKS_ID]

, [CO_Level 1]

, '' AS [FRINGE_ENTITY]

, [CO_Allocation Flag]

, [CO_Enabled Flag]

, [CO_Active Flag]

, '' AS [COMPANY]

, [CO_Expense Reports]

, '' AS [LEGACY_SET_OF_BOOKS_ID]

, [CO_New Num]

, [CO_Ledger ID]

, '' AS [STATUTORY_FLAG]

, '' AS [COMPANY_ATTRIBUTE1]

, '' AS [COMPANY_ATTRIBUTE2]

, '' AS [COMPANY_ATTRIBUTE3]

, '' AS [COMPANY_ATTRIBUTE4]

, '' AS [COMPANY_ATTRIBUTE5]

from COMPANY.qvd (qvd);

STORE COMPANY INTO 'COMPANY_double_pipe.txt' (txt, delimiter is '||');

Re: Exporting Qlikview sheet using double piple

pgrenier — Thu, 10 Oct 2013 19:47:19 GMT

Hello John,

From the QlikView online help:

Delimiter

This specifier is relevant only for delimited .txt files.

delimiter is char

For delimited table files, an arbitrary delimiter (from the 127 ASCII characters) can be specified through the delimiter is specifier, char specifies a single character.

There are some special cases:

"\t" representing a tab sign, with or without quotation marks.

"\\" representing a backslash ( \ ) character.

the word "spaces" representing all combinations of one or more spaces. Non-printable characters with an ASCII-value below 32, with the exception of CR and LF, will be interpreted as spaces.

If nothing is specified, delimiter is ',' is assumed.

So, in other words, if you define your delimiter as a two-character string, only the first shall be considered.

Maybe you could consider exporting your data with another type of delimiter, such as the tilde ~ character, if the pipe character is used within certain of your fields?

Hope this helps, regards

Philippe

Re: Re: Exporting Qlikview sheet using double piple

Anonymous — Thu, 10 Oct 2013 20:32:39 GMT

John

I am pretty sure the delimiter can only be one character and if more the one it just uses the first & ignores the rest.

You could do a frig and build a single string for yourself, like this :

BB :

load

'Field One' & '||' & 'Field Two' as Text

Autogenerate 6

;

Store BB into BB.txt (txt , delimiter is '' ) ;

It does enclose the whole thing in double quotes though, which is probably not ideal. I'd be interested if anyone knows how to get rid of them.

Best Regards, Bill

Re: Exporting Qlikview sheet using double piple

MayilVahanan — Thu, 10 Oct 2013 20:40:55 GMT

From Help, you can understand about delimiter

This specifier is relevant only for delimited .txt files.

delimiter ischar

For delimited table files, an arbitrary delimiter (from the 127 ASCII characters) can be specified through the delimiter is specifier. char specifies a single character.

There are some special cases:

"\t" representing a tab sign, with or without quotation marks.

"\\" representing a backslash ( \ ) character.

the word "spaces" representing all combinations of one or more spaces. Non-printable characters with an ASCII-value below 32, with the exception of CR and LF, will be interpreted as spaces.

If nothing is specified, delimiter is ',' is assumed.

Re: Exporting Qlikview sheet using double piple

Thu, 10 Oct 2013 20:51:19 GMT

Hi,

As you have instructed, I have made the changes as below.

load

[Essbase Member Name] & '||' & Description & '||' & [CO_To Currency Code] & '||' & [CO_Default Country Num] & '||' & ''

& '||' & [CO_Level 1] & '||' & 'NA' & '||' & [CO_Allocation Flag]

& '||' & [CO_Enabled Flag] & '||' & [CO_Active Flag] & '||' & 'NA'

& '||' & [CO_Expense Reports] & '||' & 'NA'

& '||' & [CO_New Num] & '||' & [CO_Ledger ID] & '||' & 'NA' & '||' & 'NA' & '||' & 'NA'

& '||' & 'NA' & '||' & 'NA' & '||' & 'NA'

from COMPANY1.qvd (qvd);

Extracted file looks ok but the file started with below header. Is there anyway it can be removed?

Thanks,

John

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<CreatorDoc>C:\Users\john_byun\DRM_Manipulation.qvw</CreatorDoc>

<TableName>COMPANY1</TableName>

<FieldName>[Essbase Member Name] & '||' & Description & '||' & [CO_To Currency Code] & '||' & [CO_Default Country Num] & '||' & ''

& '||' & [CO_Level 1] & '||' & 'NA' & '||' & [CO_Allocation Flag]

& '||' & [CO_Enabled Flag] & '||' & [CO_Active Flag] & '||' & 'NA'

& '||' & [CO_Expense Reports] & '||' & 'NA'

& '||' & [CO_New Num] & '||' & [CO_Ledger ID] & '||' & 'NA' & '||' & 'NA' & '||' & 'NA'

& '||' & 'NA' & '||' & 'NA' & '||' & 'NA'</FieldName>

<Type>UNKNOWN</Type>

</NumberFormat>

</QvdFieldHeader>

</Fields>

<Discriminator>c:\users\john_byun\norton zone\da vinci\drm\company1.qvd</Discriminator>

</LineageInfo>

</Lineage>

</QvdTableHeader>

Re: Exporting Qlikview sheet using double piple

Thu, 10 Oct 2013 20:52:03 GMT

Thanks for the info.

Looks like Qlikview takes only single character as a delimiter.

John

Re: Exporting Qlikview sheet using double piple

Anonymous — Thu, 10 Oct 2013 21:31:26 GMT

John

Could you reply with a copy & paste of the actual STORE command you are using.

Best Regards, Bill

Re: Exporting Qlikview sheet using double piple

Thu, 10 Oct 2013 21:36:34 GMT

COMPANY:

load

[Essbase Member Name]

, Description

, [CO_To Currency Code]

, [CO_Default Country Num]

, '' AS [SET_OF_BOOKS_ID]

, [CO_Level 1]

, '' AS [FRINGE_ENTITY]

, [CO_Allocation Flag]

, [CO_Enabled Flag]

, [CO_Active Flag]

, '' AS [COMPANY]

, [CO_Expense Reports]

, '' AS [LEGACY_SET_OF_BOOKS_ID]

, [CO_New Num]

//, [CO_Ledger ID]

, '' AS [STATUTORY_FLAG]

, '' AS [COMPANY_ATTRIBUTE1]

, '' AS [COMPANY_ATTRIBUTE2]

, '' AS [COMPANY_ATTRIBUTE3]

, '' AS [COMPANY_ATTRIBUTE4]

, '' AS [COMPANY_ATTRIBUTE5]

from COMPANY.qvd (qvd);

STORE COMPANY INTO 'Company_Ref.txt' (txt, delimiter is '||');