Set analysis and to resolve synthatic keys as one table & to make flags

[Anonymous]

Having 3 Excel files coming from 1 source all are having almost same column names need to break synthetic keys and having Code- column.As below

Office	currency	Code	Amount
10	1	GP	123
12	1	GC	234
12		AC	345
12	1	AP	1
20	-	GP	0

Need set analysis to calculate on Code column:GP or GC SUM(Amount)<-----Gross

Need set analysis to calculate on Code column:AC or AP SUM(Amount)<-----Act.

How to write syntax for a column it   Should accept only numeric value and should not be blank suppose for Office,currency columns.

[MK_QSL]

Create below two Flags inside script...

If(Len(TRIM(Office))=0 or IsNull(Office),0,1) as OfficeFlag,

If(Len(TRIM(currency))=0 or IsNull(currency),0,1) as currencyFlag,

Front End....

Inside Script, you cant used Set Analysis...

=SUM({<Code = {'GP', 'GC'},OfficeFlag = {1}, currencyFlag = {1}>}Amount)

=SUM({<Code = {'AC', 'AP'},OfficeFlag = {1}, currencyFlag = {1}>}Amount)

[Anonymous]

How to load 3 excel files which having almost same column names but i need to avoid synthatic keys and to make single table distinct columns.let say loading with flags or good or any other approach

[MK_QSL]

Kindly Provide all three files with dummy data..

[PrashantSangle]

Hi,

You can use Concatenate

If all table have same field Name then there is no need to use Concatenate

else you can use Concatenate

You have to write Concatenate between two load table

like

load*

from a;

concatenate

Load *

from B;

Regards