Multiple values in column

[souadouert]

// Boucle table

LET NumRows=NoOfRows('TAB1');

//Boucle colonne

FOR i=1 to $(NumRows)

LET ValEch=FieldValue('DATE.ECH',$(i));

let Indpt=Index('!','$(ValEch)');

let num=1;

  DO while $(Indpt)<>0

  let echance=mid($(ValEch),$(Indpt)-1);

  $(i)=$(i)+1;

  let ValEch=mid($(Indpt)+1,Len($(ValEch)));

  let nomcolonne='ECHANCE' &$(i);

  TAB2:

load

   $(echance) as $(nomcolonne),

   ID

   resident TAB1;

  loop;

NEXT

Hello,

I used this loop to load data , But in my TAB1 i have a column with multiple value , I need to add  columns as many value columns to the same table

for example

Id echane

1 2!23!45!64

i need to have a table 2

id echance1 echance2 echance3 echance4

1       2             23          45                64

[YoussefBelloum · Accepted solution]

Hi,

here is one method to do it:

subfield:

LOAD

SubField(echane,'!') as subfield,

rowno() as rowno;

LOAD Id,echane Inline [

Id, echane

1, 2!23!45!64

];

let vcountrows= Peek('rowno',-1,'subfield');

TRACE $(vcountrows);

DROP Table subfield;

FOR i=1 to $(vcountrows);

if $(i) = 1 then

test:

LOAD Id,SubField(echane,'!',$(i)) as echane$(i) Inline [

Id, echane

1, 2!23!45!64

];

ELSE

left join(test)

LOAD

Id,SubField(echane,'!',$(i)) as echane$(i) Inline [

Id, echane

1, 2!23!45!64

];

ENDIF

NEXT;

[souadouert]

Hello Youssef ,

Thank you for yr help

How to replace the inline statement with load table ?

I need to test this script with resident table

regards

[YoussefBelloum]

I think you can simply replace my first load inline, loading your final table using a Resident.

[MarcoWedel]

Hi,

another solution might be:

table1:
Generic
LOAD Id,
    'echance'&IterNo(),
    SubField(echance,'!',IterNo())   
INLINE [
    Id, echance
    1,  2!23!45!64
    2,  3!45!67
    3,  4!56!789!12!34
    4,  5!67!89!10!11!12!13   
]
While IterNo()<=SubStringCount(echance,'!')+1;

hope this helps

regards

Marco

[souadouert]

Thank you

[MarcoWedel]

you're welcome