topic Re: Multiple values in column in QlikView

Multiple values in column

souadouert — Mon, 23 Jul 2018 09:31:43 GMT

// 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),

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

Re: Multiple values in column

YoussefBelloum — Mon, 23 Jul 2018 10:24:18 GMT

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;

Re: Multiple values in column

souadouert — Mon, 23 Jul 2018 10:46:07 GMT

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

Re: Multiple values in column

YoussefBelloum — Mon, 23 Jul 2018 11:37:06 GMT

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

Re: Multiple values in column

MarcoWedel — Mon, 23 Jul 2018 23:08:45 GMT

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

Re: Multiple values in column

souadouert — Mon, 30 Jul 2018 09:15:35 GMT

Thank you

Re: Multiple values in column

MarcoWedel — Mon, 30 Jul 2018 19:48:03 GMT

you're welcome