Mapping values in one table from a second table

[#1]

Hi,

I have the following situation:

Fact:

LOAD * INLINE [

Campo1, SDV

AAA, 00350XXXX12XXXX00350XX15

BBB, 00480XX12XXXXXXXXXXXXX

AAA, XXXXXXXXX12XXXX00350XX15

];

Mapping:

LOAD * INLINE [

Codice, P_Start,           P_End,           V_Start,           V_End,           Nome

00350,           4,                               9,                     13,                              15,                    Importo

00350,           16,                     21,           23,                              25,                    Importo

00480,           4,                               9,                     11,                              13,                    Area

];

I need to read the Fact table and, for every line I need to check SDV like this:

     if(mid(SDV, P_Start, P_End) = Codice, mid(SDV, V_Start, V_End) as Nome

mean that the value of the code 00350 is in the position 13 to 15 in SDV and must be called Importo.

Any idea?

Thanks!

Andrea

[stephencredmond]

Hi Andrea,

How about creating the cartesian join:

Fact:

LOAD * INLINE [

    Campo1, SDV

    AAA, 00350XXXX12XXXX00350XX15

    BBB, 00480XX12XXXXXXXXXXXXX

    AAA, XXXXXXXXX12XXXX00350XX15

];

join

LOAD * INLINE [

    Codice, P_Start, P_End, V_Start, V_End, Nome

    00350, 4, 9, 13, 15, Importo

    00350, 16, 21, 23, 25, Importo

    00480, 4, 9, 11, 13, Area

];

Then, you can compare line by line?

Regards,

Stephen

[#3]

Hi Stephen,

good that's work but now I need that the filed name of the resulting table is like the filed Nome. For istance:

if(mid(SDV, P_Start, P_End) = Codice, mid(SDV, V_Start, V_End) as Nome

Any idea?

Thanks!

[#4]

Hi, no ideas on that?

Thanks!

Andrea