Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try replacing count(BrAccount) with aggr(Count(BrAccount),TlrNbr,TlrName)
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.
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!
In Qlikview, we don't have data types for fixed length fields. I suggest Please follow gwassenaarMethod or create the fixed length fields in the script it self.
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;
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.
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.
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!
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.
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.
Try replacing count(BrAccount) with aggr(Count(BrAccount),TlrNbr,TlrName)