topic Re: File Target Endpoint : explicit control of column sequence in Qlik Replicate

File Target Endpoint : explicit control of column sequence

bengseng — Wed, 17 Feb 2021 06:09:34 GMT

I am using the file target endpoint to output CSV files from database table. These CSV files are use as input to another software system. The interface specification demand that the columns are followed according to document.

For example: Table "User", has 3 columns "Name", "Age", "Gender"

The CSV file must be generated with columns in this sequence. i.e.
"Name","Age","Gender"
"John","13","Male"
"Dave","14","Male"
"Jane","12","Female"

In , I am able to remove columns that are not required. But, I do not see how I can explicitly manage the column order. So, sometime after some schema update in the source database table, I might get:
"Name","Gender","Age"
"John","Male","13"
"Dave","Male","14"
"Jane","Female","12"

which is not what is agreed upon. Any advise on how I can control the column sequence order?

Re: File Target Endpoint : explicit control of column sequence

lyka — Fri, 19 Feb 2021 22:14:14 GMT

Hello,

Unfortunately there is no way to control the order of columns for a File Target, unlike with let's say an Oracle or SQL Server target where in you can create the table manually in the target with the desired column order then set Replicate as Truncate before Loading.

I suggest submitting a feature request thru Ideation if this is a required use case.

Thanks

Lyka

Re: File Target Endpoint : explicit control of column sequence

qlik-lmy-se — Thu, 25 Feb 2021 21:11:04 GMT

Hey Lyka,

I am also not able to find the headers in the target CSV file, which is super confusing, is there a way to add table header on the list? Thank you.

--Mengyun

Re: File Target Endpoint : explicit control of column sequence

lyka — Fri, 26 Feb 2021 01:10:59 GMT

Hi Mengyun,

What table header are you looking for? If metadata header, you can enable that on the target endpoint definition

Thanks

Lyka

Re: File Target Endpoint : explicit control of column sequence

bengseng — Fri, 26 Feb 2021 01:18:19 GMT

As mentioned by Lyka, you can do so at the Manage Endpoints Connections under the Add metadata header section.

Re: File Target Endpoint : explicit control of column sequence

qlik-lmy-se — Fri, 26 Feb 2021 02:04:24 GMT

Hey Bengseng,

Thanks for your reply, the problem is that I could not find this option for me , either on the Manage Endpoints Connections or the task settings, my source point is from DB2 z/OS, even in Advanced tab:

Re: File Target Endpoint : explicit control of column sequence

john_wang — Fri, 26 Feb 2021 03:46:55 GMT

Hello @bengseng @lyka @qlik-lmy-se ,

I found an easy way to control the columns sequence for generic File Target Endpoint. The basic idea is using table's transformation to adjust the order of the desired columns.

I'm using Oracle source (the solution is irrelevant to the Source Endpoing Type) and File Target to demo how to control the columns order.

In my demo the source table looks like:

create table testcols (id integer, name char(20), address char(20), address2 char(20));

In the demo we are assuming the desired order of the columns in File Target become:

ID, address, address2, name

Please take note some columns order maybe cannot be adjusted if they are part of the PK (In my sample I'm assuming the column "ID" cannot be adjusted) because they cannot be removed from the transformation. So the PK columns will appear prior to other general columns.

The steps are:

1. Remove all the columns from Transform (keep column "ID" as is because it's PK)

2. Add a new column (you choose a name for it) and the expression is simple: you add the original column name as the expression (certainly you can compose your owner expression if needed)

You see I added three new columns. For example one of the new columns name is "2" and its expression is "$ADDRESS".

3. You can see the results in both FL file and __ct table as below:

BTW, for your question where you can add the column header:

Hope this helps.

Regards,

John WANG.

Re: File Target Endpoint : explicit control of column sequence

lyka — Fri, 26 Feb 2021 04:35:39 GMT

Hello,

I tested this with version 7 and tried to change the order of columns but it didnt work.

I'll test this again tomorrow.

thanks
lyka