Invalid Expression

[#1]

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;

[tresesco]

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, ...

[Anil_Babu_Samineni]

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;

[#4]

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