Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to generate text files delimited by tabulations based upon a table in a QV application.
This Table contains over 6000 rows, but into these rows there are only 6 departments, I would like generate one file per department.
The name of the file will be the department name ('Department'_Actuals.txt) and I don't want export the labels into the files.
To do that, I load my table like this :
ForfaitsTmp:
CrossTable (Mois,Montant)
LOAD
Type & '|' & CodeClient & '|' & Media & '|' & Dépt & '|' & Fichier as key,
Jan,
Fev,
Mar,
Avr,
Mai,
Juin,
Juil,
Aou,
Sep
FROM '$(vQVDPath)\$(vProjet)\forfait.xls'
(biff, embedded labels, table is Sheet1$);
Forfaits:
Load SubField(key,'|',1) as Type,
SubField(key,'|',2) as CodeClient,
SubField(key,'|',3) as Media,
SubField(key,'|',4) as Dépt,
SubField(key,'|',5) as Fichier,
Mois, num(Montant,'# ##0.00000')
RESIDENT ForfaitsTmp;
STORE Forfaits INTO '$(vQVDPath)\$(vProjet)\'Department name'_Actuals.txt' (delimiter is '\t');
I'm not quite good in scripting, can somebody help me pls ?
Thanks,
You could by crosstable-load the number of columns specify which will be loaded normally and you didn't need to create this key and split it then again. Try therefore the following:
ForfaitsTmp:
CrossTable (Mois,Montant, 5)
LOAD * FROM [$(vQVDPath)\$(vProjet)\forfait.xls] (biff, embedded labels, table is Sheet1$);
for i = 1 to fieldvaluecount('Dept')
let vTable = fieldvalue('Dept', $(i))
$(vTable):
Load Type, CodeClient, Media, Dept, Fichier, Mois, num(Montant,'# ##0.00000') as Montant
Resident ForfaitsTmp;
store $(vTable) into [$(vQVDPath)\$(vProjet)\ $(vTable)_Actuals.txt] (txt, no labels, delimiter is '\t');
next
The store-statement to text is limited and I'm not sure if it's considered the additionally entries with labels and delimiter but in general it should work.
- Marcus