Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
souadouert
Specialist
Specialist

Multiple values in column

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

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

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;

View solution in original post

6 Replies
YoussefBelloum
Champion
Champion

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
Specialist
Specialist
Author

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
Champion
Champion

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

MarcoWedel

Hi,

another solution might be:

QlikCommunity_Thread_308671_Pic1.JPG

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
Specialist
Specialist
Author

Thank you

MarcoWedel

you're welcome