topic Invalid Expression in QlikView

Invalid Expression

Wed, 25 Nov 2020 16:16:04 GMT

Hello there,

Is it possible to aggregate data when concatenating QVDs in different folders as below? My script was working well before I aggreated data, but now it gives me an Invalid Expression when there is aggregation.

sub ScanFolder(Root)

for each FileExtension in 'qvd'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileList:

$(vConcatenate)

LOAD COUNTRY,

ACCOUNT_NO,

CUSTOMER_ID,

TXN_BRANCH_NAME,

DOM_BRANCH_NAME,

CURRENCY,

sum(TXN_AMOUNT_BOTH) as TXN_AMOUNT_BOTH,

sum(VOLUME_OF_TRANS) as Amount,

PERIOD,

TRAN_TYPE,

TRANSACTION_TYPE,

E_CHANNEL

FROM [$(FoundFile)] (qvd)

Group by COUNTRY,

CUSTOMER_ID,

TXN_BRANCH_NAME,

DOM_BRANCH_NAME,

CURRENCY,

PERIOD,

//date(Date#(PERIOD,'YYYYMMDD'),'YYYYMM'),

TRAN_TYPE,

TRANSACTION_TYPE,

E_CHANNEL;

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('C:\Users\C741147\Desktop\QVData\ATM_POS') ;

store FileList into 'C:\Users\C741147\Desktop\QVData\CONSOLIDATED_QVDS\ATM_POS.qvd'(qvd);

drop Table FileList;

Re: Invalid Expression

tresB — Wed, 19 Jul 2017 09:24:49 GMT

You have to bring all dimensions that are not aggregated under Group By clause. Try like:

....

LOAD COUNTRY,

ACCOUNT_NO,

CUSTOMER_ID,

TXN_BRANCH_NAME,

DOM_BRANCH_NAME,

CURRENCY,

sum(TXN_AMOUNT_BOTH) as TXN_AMOUNT_BOTH,

sum(VOLUME_OF_TRANS) as Amount,

PERIOD,

TRAN_TYPE,

TRANSACTION_TYPE,

E_CHANNEL

FROM [$(FoundFile)] (qvd)

Group by COUNTRY, ACCOUNT_NO, CUSTOMER_ID, ...

Re: Invalid Expression

Anil_Babu_Samineni — Wed, 19 Jul 2017 09:25:54 GMT

Looks like, you are doing wrong place

sub ScanFolder(Root)

for each FileExtension in 'qvd'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileList:

$(vConcatenate)

LOAD COUNTRY,

ACCOUNT_NO,

CUSTOMER_ID,

TXN_BRANCH_NAME,

DOM_BRANCH_NAME,

CURRENCY,

sum(TXN_AMOUNT_BOTH) as TXN_AMOUNT_BOTH,

sum(VOLUME_OF_TRANS) as Amount,

PERIOD,

TRAN_TYPE,

TRANSACTION_TYPE,

E_CHANNEL,

CUSTOMER_ID,

TXN_BRANCH_NAME,

DOM_BRANCH_NAME,

CURRENCY,

PERIOD,

//date(Date#(PERIOD,'YYYYMMDD'),'YYYYMM'),

TRAN_TYPE,

TRANSACTION_TYPE,

E_CHANNEL

FROM [$(FoundFile)] (qvd)

Group by COUNTRY, ......; // I think you must do all non aggr fields in Group clause

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('C:\Users\C741147\Desktop\QVData\ATM_POS') ;

store FileList into 'C:\Users\C741147\Desktop\QVData\CONSOLIDATED_QVDS\ATM_POS.qvd'(qvd);

drop Table FileList;

Re: Invalid Expression

Wed, 19 Jul 2017 09:46:45 GMT

Hi ,

I have done exactly what said I should do but it’s still giving me the same error.

CURRENCY,

sum(TXN_AMOUNT_BOTH) as TXN_AMOUNT_BOTH,

sum(VOLUME_OF_TRANS) as Amount,

PERIOD,

TRAN_TYPE,

TRANSACTION_TYPE,

E_CHANNEL

FROM (qvd)

Group by COUNTRY,CUSTOMER_ID,TXN_BRANCH_NAME,DOM_BRANCH_NAME,CURRENCY,PERIOD,TRAN_TYPE,TRANSACTION_TYPE,E_CHANNEL

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

call ScanFolder(SubDirectory)

next SubDirectory

end sub