Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
alexpanjhc
Valued Contributor

why is it not concatenating?

I am trying to concatenate all my files in a folder like this :

for each Dir in dirlist ('$(vDataFilePath)'&'\*' )

trace $(Dir);


for each file in FileList('$(Dir)/*.*')


trace 'file is' $(file);

   A:

    load

*

    from [$(file)]

   (ooxml, embedded labels, header is 7 lines, table is [F Data]);

next file;


When I load data, i got error messages below, what would be the problem.


the files all have the same number of field names and the field names are the same.

Also how to store the files into different qvd based on the file name.

for example:

file name is CLP2 - 2016 Dec 31 Tax Estimate Allocations.xlsm

I want to get everything before year,like: CLP2 - 2016.qvd, CLP2 - 2017.qvd However the name CLP2 is not fixed it can be longer sometimes.

Started loading data

lib://ZHPAN (ad_zhpan)/Data/C

'file is' lib://ZHPAN (ad_zhpan)/Data/C/CLP2 - 2016 Dec 31 Tax Estimate Allocations.xlsm ' 

A << F Data

Lines fetched: 140

'file is' lib://ZHPAN (ad_zhpan)/Data/C/CLP2 - 2017 Dec 31 Tax Estimate Allocations.xlsm '

A-1 << F Data Lines fetched: 111 'file is' lib://ZHPAN (ad_zhpan)/Data/C/CLP2 - 2018 Jul 31 Tax Estimate Allocations.xlsm '

A-2 << F Data

Lines fetched: 116 $Syn 1 = PTDI_PTRNUM+PTDI_PTRKEYID+PTDI_OKID1+PTDI_OKID2+PTDI_NAME1+PTDI_NAME2+PTDI_DOMFOR+PTDI_USRESID+PTDI_ENTTYPE+FDK1_BOYPPCT+FDK1_BOYLPCT+FDK1_BOYCAP+FDK1_EOYPPCT+FDK1_EOYLPCT+FDK1_EOYCAP+FDK1_NONRECRSE+FDK1_QLNONRECRSE+FDK1_RECRSE+FDK1_BEGCAP+FDK1_CONTR+FDK1_PTRINC+FDK1_WITHDWLS+FDK1_ENDCAP+FDK1_GAINPROP+FDK1_FINAL+FDK1_AMEND+FDK1_ORDINC+FDK1_RRE+FDK1_OTHRENT+FDK1_GRNTPAY+FDK1_INTINC+FDK1_ORDDIV+FDK1_QUALDIV+FDK1_ROY+FDK1_NSTGL+FDK1_NLTGL+FDK1_COLGL+FDK1_1250GL+FDK1_1231GL+FDK1_OTRPORT+FDK1_INVOLCONV+FDK1_1256+FDK1_MNGCOSTREC+FDK1_CANDEBT+FDK1_OTHINC+FDK1_179DED+FDK1_CASHCONT50+FDK1_CASHCONT30+FDK1_NCASHCONT50+FDK1_NCASHCONT30+FDK1_CGPROP50+FDK1_CGPROP20+FDK1_CONT100+FDK1_INVINTEXP+FDK1_DEDROYINC+FDK1_59E2EXP+FDK1_DEDPORT2+FDK1_DEDPORTOTH+FDK1_AMTMEDINS+FDK1_EDUASSBEN+FDK1_DEPCAREBEN+FDK1_PREPEREXP+FDK1_COMREVIT+FDK1_PENIRA+FDK1_REFOREXPDED+FDK1_DOMPRODACT+FDK1_QUALPRODINC+FDK1_EMPW2WAGE+FDK1_OTHDED+FDK1_SELFEMPEARN+FDK1_FARMFISHINC+FDK1_NFARMINC+FDK1_42J5PRE08+FDK1_OTHPRE08+FDK1_42J5POST07+FDK1_OTHPOST07+FDK1_RREEXP+FDK1_OTHRRECR+FDK1_OTHRENTCR+FDK1_UNDISCGCR+FDK1_FUELCR+FDK1_OPPCR+FDK1_DISACCCR+FDK1_EZRCECR+FDK1_RESACTCR+FDK1_SSMEDCR+FDK1_BACKUPWH+FDK1_OTHCR+FDK1_COUNTRY+FDK1_GROSSINC+FDK1_PTNRINC+FDK1_PASSIVE+FDK1_GENERAL+FDK1_OTHER+FDK1_INTEXP+FDK1_OTHER2+FDK1_PASSIVE2+FDK1_GENERAL2+FDK1_OTHER3+FDK1_FTAXPAID+FDK1_FTAXACC+FDK1_TAXRED+FDK1_FTRADEGROSS+FDK1_EXTINCEXC+FDK1_OTHFTRANS+FDK1_86DEPADJ+FDK1_ADJGL+FDK1_DEPLETION+FDK1_OGGINC+FDK1_OGGDED+FDK1_OTHAMT+FDK1_TAXEXEINT+FDK1_OTHTAXEXE+FDK1_NDEDEXP+FDK1_CMSEC+FDK1_737+FDK1_OTHPROP+FDK1_INVINC+FDK1_INVEXP+FDK1_FUELCRINF+FDK1_QUALREHEXP+FDK1_BASISEPROP+FDK1_RECAP42J5+FDK1_RECAPOTH+FDK1_RECAPINVCR+FDK1_RECAPOTHCR+FDK1_LBILT+FDK1_LBIIFM+FDK1_DIS179DED+FDK1_RECAP179DED+FDK1_INTEXPPTNR+FDK1_453L3INF+FDK1_453ACINF+FDK1_1260BINF+FDK1_INTPRODEXP+FDK1_CCFNQW+FDK1_DEPLOG+FDK1_AMORTREFORE+FDK1_UBTI+FDK1_PRECONTGL+FDK1_108IINFO+FDK1_NETINVINC+FDK1_OTHINFO+FDK1_TAXINC+FDK1_ANALYSISPTYPE+FDK1_CORP+FDK1_DISENTITY+FDK1_ESTATE+FDK1_FIDUCIARY+FDK1_GRANTORTRUST+FDK1_INDIV+FDK1_LLC+FDK1_NOMINEE+FDK1_PSHIP+FDK1_IRA+FDK1_SCORP+FDK1_TRUST+FDK1_CYINCDEC_1+FDK1_CYINCDEC_2+FDK1_CYINCDEC_3+FDK1_CYINCDEC_4+FDK1_CYINCDEC_5+FDK1_CYINCDEC_6+FDK1_CYINCDEC_7+FDK1_CYINCDEC_8+FDK1_CYINCDEC_9+FDK1_CYINCDEC_10+FDK1_CYINCDEC_11+FDK1_CYINCDEC_12+FDK1_CYINCDEC_13+FDK1_CYINCDEC_14+FDK1_CYINCDEC_15+FDK1_ORDIPASSIVE_1+FDK1_ORDINONPASSIVE_1+FDK1_GOVTINTINC_5+FDK1_ISSDISUS_5+FDK1_ISSDISFN_5+FDK1_MRKTDISUS_5+FDK1_MRKTDISFN_5+FDK1_OTHINTINC_5+FDK1_OTHINTINCFN_5+FDK1_DIVINCUS_6A+FDK1_DIVINCFN_6A+FDK1_DIVINCUS_6B+FDK1_DIVINCFN_6B+FDK1_OTHERSHTCG_8+FDK1_SHTCGDIS_8+FDK1_OTHERLTCG_9+FDK1_LTCGDIS_9+FDK1_988NONMAJINCLOSS_11A+FDK1_988MAJINCLOSS_11A+FDK1_987INCLOSS_11A+FDK1_SWAPINCAG_11A+FDK1_SWAPINCNONAG_11A+FDK1_11ACUSTOM_1+FDK1_1256TRAD+FDK1_1256INVEST+FDK1_ORDINCTRAD+FDK1_STCAPGAINLOSS+FDK1_SHTCGLDIS+FDK1_LTCAPGAINLOSS+FDK1_LTCGLDIS+FDK1_11FCUSTOM_1+FDK1_GOVTINTINC_11F+FDK1_ORGDISUS_11F+FDK1_ORGDISFN_11F+FDK1_MKTDISUS_11F+FDK1_MKTDISFN_11F+FDK1_OTHINTINC_11F+FDK1_OTHINTINCFN_11F+FDK1_DIVINCNONQUAL+FDK1_DIVINCNONQUALFN_11F+FDK1_DIVINCQUAL+FDK1_DIVINCQUALFN_11F+FDK1_988NONMAJINCLOSS_11F+FDK1_988MAJINCLOSS_11F+FDK1_987INCLOSS_11F+FDK1_TRADBUSEXP+FDK1_OTHTRADBUSEXP+FDK1_ORDINCMTMELEC+FDK1_SWAP111F+FDK1_SWAP211F+FDK1_11FCUSTOM_2+FDK1_11FCUSTOM_3+FDK1_INVINTEXPINT+FDK1_INVINTEXPTRAD+FDK1_MGMTFEE+FDK1_SWAP113K+FDK1_SWAP213K+FDK1_13KCUSTOM_1+FDK1_13KCUSTOM_2+FDK1_13KCUSTOM_3+FDK1_13KCUSTOM_4+FDK1_13WCUSTOM_1+FDK1_13WCUSTOM_2+FDK1_13WCUSTOM_3+FDK1_13WCUSTOM_4+FDK1_USWTHTAX+FDK1_15PCUSTOM_1+FDK1_UBTIORD+FDK1_UBTISTCAP+FDK1_UBTILTCAP+FDK1_DIVDED+FDK1_FORQUALDIV+FDK1_EXPUSGOVTOBL+FDK1_INTUSGOVTOBL+FDK1_USGOVTDIVINC+FDK1_QUALDIVUBTI+FDK1_TOTFORDIV+FDK1_BONUSDEP2011+FDK1_BONUSDEP2012+FDK1_BONUSDEP2013+FDK1_BONUSDEP2014+FDK1_BONUSDEP2015+K1SUITEEND+FDK1_BONUSDEP2016

Creating search index Search index creation completed successfully

App saved

Finished successfully

0 forced error(s)

1 synthetic key(s)

Tags (1)
1 Solution

Accepted Solutions
alexpanjhc
Valued Contributor

Re: why is it not concatenating?

Thanks Gabor,

I Listed all fields that I need one by one and that resolved the problem also

I also tried

set VConcat=;

A:

   $(VConcat)

load * from

$(files)

  (ooxml, embedded labels, header is 7 lines, table is [Federal Data]);

set VConcat=Concatenate;

  

next file;

but the cloud does not recognize this it will show you red, but if i run it, it can be concatenated without error.

Maybe a bug ? not sure.

thanks for you help!

3 Replies
undergrinder
Valued Contributor II

Re: why is it not concatenating?

Hi Zhen,

It seems one or multiple file has different column though.

You can check it at data model viewer. You should see a table name A and a table name A-1 and A-2, the tables are linked by a synthetic table. The rest of the fields are the difference.

The files are excel, may be there are empty columns, that are used in the files. In this case the columns are loaded with @1, @2 or A, B,C fieldname and all data is null.

I recommend you to delve into those files.

G.

alexpanjhc
Valued Contributor

Re: why is it not concatenating?

Thanks Gabor,

I Listed all fields that I need one by one and that resolved the problem also

I also tried

set VConcat=;

A:

   $(VConcat)

load * from

$(files)

  (ooxml, embedded labels, header is 7 lines, table is [Federal Data]);

set VConcat=Concatenate;

  

next file;

but the cloud does not recognize this it will show you red, but if i run it, it can be concatenated without error.

Maybe a bug ? not sure.

thanks for you help!

undergrinder
Valued Contributor II

Re: why is it not concatenating?

Hi Zhen,

I observed similar issue with the data load editor as well, it highlight as syntax error, but the engine can run it.

For example: let test = 'test123 ''abc'' test 123'  - in this case the ' character was exited in string, but the editor parse it as error.

Don't worry about it, if it runs correctly.

G.