Exporting to TXT File -- Load Column mismatch

[#1]

Hello all,

I am having an issue, I was hoping someone might be able to assist with.

I have 3 sources (excel) NON CSV, I have brought them in, converted to QVD's and I have joined fields within those QVD's in order to make one customized table with data from all 3 excel sources.

The problem is, when I export the txt file, it doesn't load the fields in the correct Load Order, instead it flips the first and second column eventhough the order is correct in the data model view.

My goal is to export as a txt file and bulk insert into a database which has hard-coded fields set up in a specific order.

I'll include some code and screenshots as well to give you guys a better idea...

This is where I am joining to make one central table called KE_Data, this is the same file that I wish to bulk insert to database.

I have a feeling the issue is the Group by because KE Task ID is being exported as the first column, when it should be after {Known Error ID] which is a Primary Key on the "ke_root.qvd"

KE_DATA:

LOAD *

FROM [\\t\RootCauseETL\ke_root.qvd] (qvd);

Left Joi

LOAD *

FROM [\\t\RootCauseETL\ke_task.qvd] (qvd);

Left Join (KE_DATA)

LOAD [KE Task ID],

  Max([Last KE Task Work Info Date Q5]) as [Last KE Task Work Info Date Q5],

     Max([Last KE Task Work Info Mod Date Q5]) as [Last KE Task Work Info Mod Date Q5]

FROM

[\\t\RootCauseETL\ke_task_work_info.qvd] (qvd)

Group By [KE Task ID];

STORE KE_DATA INTO [\\t\[RootCauseETL\KE_Data.qvd] (qvd);

STORE KE_DATA INTO [\\t\RootCauseETL\KE_Data.txt] (txt, delimiter is '~');

TLDR: How can I specifically assign column sort when exporting to a txt file.

Thanks!

[#2]

Forgot to mention that I am a beginner so if anything looks wrong in my code or if you have any 'best practices' suggestions, feel free to leave them below even if you don't have a direct answer to my question.

[settu_periasamy]

Hi

Nicholas Colino wrote:

TLDR: How can I specifically assign column sort when exporting to a txt file.

You mean column order?

You can give the field names instead of using * (load all fields)

like

KE_DATA:

LOAD *

FROM [\\t\RootCauseETL\ke_root.qvd] (qvd);

Left Join

LOAD *

FROM [\\t\RootCauseETL\ke_task.qvd] (qvd);

Left Join (KE_DATA)

LOAD [KE Task ID],

  Max([Last KE Task Work Info Date Q5]) as [Last KE Task Work Info Date Q5],

     Max([Last KE Task Work Info Mod Date Q5]) as [Last KE Task Work Info Mod Date Q5]

FROM

[\\t\RootCauseETL\ke_task_work_info.qvd] (qvd)

Group By [KE Task ID];

NoConcatenate

KE_DATA_New:

LOAD Field1,

          Field2,

          Field3  

Resident KE_DATA;

DROP Table KE_DATA;

STORE KE_DATA_New INTO [\\t\[RootCauseETL\KE_Data_New.qvd] (qvd);

STORE KE_DKE_DATA_New INTO [\\t\RootCauseETL\KE_Data_New.txt] (txt, delimiter is '~');

[marcus_sommer]

I don't know how qlikview handled the order of fields when tables are merged - you could do a step by step check while loading and merging your tables with:

for i = 1 to nooffields('KE_DATA')

     let vFieldOrder = '$(vFieldOrder)' & ' + ' & fieldname($(i), 'KE_DATA');

next

trace '$(vFieldOrder)';

Should be the order of the fields change between the loadings and/or don't suits your expectations you could try and change the order of the fields while loading by specifying them directly (maybe in an additionally load like suggested from settu_periasamy) or you could specify the order directly within the store-statement:

store field1, field2, ... from KE_DATA into [\\t\RootCauseETL\KE_Data.txt] (txt, delimiter is '~');

- Marcus