Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Export to text file with fixed length data

Hi

I have written a macro to export the straight table in a qvw to a flat file (.txt) file and was able to specify the delimiter i wish to.

The macro which i used is as follows:

sub Export

Dim docProp

set docProp = ActiveDocument.GetProperties

Dim tmpFile  'used to create relative filepaths

tmpFile = docProp.MyWorkingDirectory

tmpFile = tmpFile & "External Data Sources\OperationalReportsOutput\"

set obj = ActiveDocument.GetSheetObject("CH01")

obj.Export tmpFile & "Activity" & ".txt",";"

end sub

Now i have to change the output data to a fixed length datatype. It means for suppose, i have 3 fields

Name  Address              Phonenumber

Sam    Greenville,USA    2159627856

Vizard Charlotte, USA    2527255632

If i gave a fixed length of 20 to the fields:and i specify no delimiter,

the output should look like

Sam                 Greenville,USA      2139627856

Vizard               Charlotte,USA       2527255632

So every record in the text file should end at the same point.

Any ideas!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try replacing count(BrAccount) with aggr(Count(BrAccount),TlrNbr,TlrName)


talk is cheap, supply exceeds demand

View solution in original post

12 Replies
Gysbert_Wassenaar

You could do this by creating a straight table with only one expression that concatenates the fields into one string :

= left(Name,20) & repeat(' ', 20-len(Name1)) &  left(Address,20) & repeat(' ', 20-len(Address)) &  left(Phonenumber,20) & repeat(' ', 20-len(Phonenumber)).

Then export that straight table.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Hi Gysbert Wassenaar,

That was a helpful solution, but they just want to know is it possible to do it in the macro so that the output file shows the data in the way they wish, without any change to the straight table

How can i specify it in the macro! i have no idea!

Not applicable
Author

In Qlikview, we don't have data types for fixed length fields. I suggest Please follow

LOAD IF(Len(Name) > 20, LEFT(Name,20) , Name & Repeat(' ', 20-Len(Name)) ) AS Name,

          IF(Len(Address) > 20, LEFT(Address,20) , Address& Repeat(' ', 20-Len(Address)) ) AS Address,

          IF(Len(Phonenumber) > 20, LEFT(Phonenumber,20) , Name & Repeat(' ', 20-Len(Phonenumber)) ) AS Phonenumber

SOURCE;

Gysbert_Wassenaar

You don't have to change the original straight table. Create another straight table and hide it. Just make the button (or whatever) that calls the macro export the hidden straight table instead of exporting the original one.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Hi Dathu

Thank you for your suggestion. I don't need the fixed length in qlikview. What i am asking is can we define the fixed length field while exporting to a text file. I mean it in the macro script. Just want to know can we do it in vbscript! If no other option is availabe, i can go with Gysbert Wassenaar's method.

Anonymous
Not applicable
Author

Gysbert Wassenaar

I have tried the method you said, by creating a new straight table with just one expression. But it is showing the output only when one Name or only one phonenumber is selected. without any selections, it is showing nothing!

Do i need to change something!

Gysbert_Wassenaar

Ah, my mistake. Here's what you can do: Create a calculated dimension using the expression I posted. Then add an expression to the straight table, for example just 1 as expression will do. Set the total mode to None and then hide the expression on the presentation tab. The straight table with just that calculated dimension and the hidden expression should result in a table that will export to the format you want.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

I am using a aggregation function in the expression.

=  left(TlrNbr,20) & repeat(' ', 20-len(TlrNbr)) &  left(TlrName,20) & repeat(' ', 20-len(TlrName)) & left(Count(BrAccount),20) & repeat(' ', 20-len(Count(BrAccount)))

So, i think we cannot use it as a calculated dimension.

Gysbert_Wassenaar

Try replacing count(BrAccount) with aggr(Count(BrAccount),TlrNbr,TlrName)


talk is cheap, supply exceeds demand