Problem with field

[#1]

Hello Everyone,

I have data as follows

Empid,ShiftAllocated

1,1

2,12

3,13

4,31

5,123

i want this data as

Empid,ShiftAllocated

1,1

2,1

2,2

3,1

3,3

4,3

4,1

5,1

5,2

5,3

Please help me with this problem

Thanks and Regards,

Priya

[MK_QSL]

what is the logic behind this result?

[#3]

i think it is problem of delimiter

so if u upload Sample Data file It will be Easy For

us  to give Solution

it is it .Csv or .txt type file ?

[Aurelien_Martinez]

Hi,

DATA:

LOAD * Inline

[

Empid,ShiftAllocated

1,1

2,12

3,13

4,31

5,123

];

for i=0 to NoOfRows('DATA') - 1

  Let vEmpid = Peek('Empid', $(i), 'DATA');

  Let vLen = len(Peek('ShiftAllocated', $(i), 'DATA'));

  For j=1 to $(vLen)

    DATA2:

    LOAD

      $(vEmpid) as TEST,

      Mid(ShiftAllocated, $(j), 1) as TEST2

    Resident DATA

    Where Empid = $(vEmpid)

    ;

  next j 

next i

Best,

Aurélien

[er_mohit]

Try this code into your script

//if there is delimiter then try this

Data:

LOAD SubField(ShiftAllocated,',') as New,* INLINE [

    Empid, ShiftAllocated

    1, 1

    2, "1,2"

    3, "1,3"

    4, "3,1"

    5, "1,2,3"

];

[maxgro · Accepted solution]

RESULT

Empid	ShiftAllocated
1	1
2	1
2	2
3	1
3	3
4	1
4	3
5	1
5	2
5	3

SCRIPT

// add a separator @ to single char (1,2,3) with mapsubstring

// remove last separator

// use subfield  to generate rows

map1:

mapping load * inline [

1, 1@

1, 1@

2, 2@

3, 3@

] ;

S:

load Empid, ShiftAllocated, left(Newcol, len(Newcol)-1) as Newcol;

load Empid, ShiftAllocated, MapSubstring ('map1', ShiftAllocated) as Newcol;

load * inline [

Empid, ShiftAllocated

1,1

2,12

3,13

4,31

5,123

];

T:

NoConcatenate

load Empid, SubField(Newcol, '@') as ShiftAllocated

Resident S;

DROP Table S;