Keep dual number values

[Anonymous]

Hi, community!

Do anyone had practice how to save second part (number) of dual values in the script after several data transformation?

For example, in first step i define dual values using excel source:

Temp:

LOAD

if(Name='A-1', dual('A-1',1),

if(Name='B-2', dual('B-2',2),

if(Name='C-56', dual('C-56',3),

if(Name='D-44', dual('D-44',4))))) as Name

FROM

Dual.xlsx

(ooxml, embedded labels, table is Sheet1);

First result is ok:

But after some data transformation dual sorting is missing in the result:

Result:

NoConcatenate

load

if(Name='D-44', 'Last' & Name,

subfield(Name,'-',-1)) as Name

Resident Temp;

DROP Table Temp;

Any idea how to keep number dual values in this case?

Thanks.

[woshua5550]

why not do the data transformation at step 1? this will be ok :

LOAD

if(Name='A-1', dual('A-1',1),

if(Name='B-2', dual('B-2',2),

if(Name='C-56', dual('C-56',3),

if(Name='D-44', dual('Last' & Name,4)))))

FROM

Dual.xlsx

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

because it's the idea of real app - i shouldn't do it in the first step

[avinashelite · Accepted solution]

if that is the case define it in the level two like this

Result:

NoConcatenate

load

if(Name='D-44',Dual( 'Last' & Name,num(subfield(Name,'-',-1))) ) as Name

Resident Temp;

DROP Table Temp;

[Anonymous]

Thanks!) Perfect!