topic Problem with field in QlikView

Problem with field

Mon, 03 Mar 2014 11:37:12 GMT

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

Re: Problem with field

MK_QSL — Mon, 03 Mar 2014 11:44:28 GMT

what is the logic behind this result?

Re: Problem with field

Mon, 03 Mar 2014 11:47:41 GMT

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 ?

Re: Problem with field

Aurelien_Martinez — Mon, 03 Mar 2014 11:53:58 GMT

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

Re: Problem with field

er_mohit — Mon, 03 Mar 2014 11:59:55 GMT

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"

];

Re: Problem with field

maxgro — Mon, 03 Mar 2014 12:04:50 GMT

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@

2, 2@

3, 3@

] ;

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

];

NoConcatenate

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

Resident S;

DROP Table S;