How to remove synthetic keys from generic load ?

[agni_gold]

I have applied simple generic load , it makes lot of tables (for that field values) and synthetic keys , how can i remove them ?

ID	Name	Rate	Region	Value
1	Agnivesh	3	Delhi	10
2	Kumar	6	Delhi	15
3	Kanpur	9	Jaipur	20
4	UP	12	Lucknow	25
5	LKO	15	Lucknow	30
Need Output Like
ID	Name	Rate	Delhi	Jaipur	Lucknow
1	Agnivesh	3	10
2	Kumar	6	15
3	Kanpur	9		20
4	UP	12			25
5	LKO	15			30

So i have done Simply

A:

Generic LOAD ID,     

     Rate,     

     Name,

     Region,

     Value

FROM

(ooxml, embedded labels, table is Sheet2);

[satishqlik · Accepted solution]

Hi

You can achieve by this

inputtab:

LOAD id,

     name,

     rate,

     region,

     value

FROM

C:\Users\admin\Desktop\QV\genricload.xlsx

(ooxml, embedded labels, table is Sheet1);

gentab:

Generic

LOAD id,

     name,

     rate,

     region,

     value

     Resident inputtab;

resulttab:

LOAD Distinct id,name,rate Resident inputtab;

FOR i=0 to NoOfTables()

tablelist:

LOAD TableName($(i)) as tablename AutoGenerate 1

Where WildMatch(TableName($(i)),'gentab.*');

NEXT i

FOR i=1 to FieldValueCount('tablename')

LET Vtable=FieldValue('tablename',$(i));

Left Join (resulttab)

LOAD * Resident [$(Vtable)];

DROP Table [$(Vtable)];

NEXT i

DROP Tables tablelist,inputtab; 

I hope this helps you!

Rgs

satish