Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

SubField Function

Guys,

I have the sample data below,

Screenshot_1.png

I can separate these fields by using subfield function while loading. But I want to know whether it is possible to get the below result,

1. Field1 should be mapped only with String.

2. Field2 should be mapped only with Num and

3. Field3 should be mapped with Boolean.

No other combinations possible.

Please share if it is possible or I'm wrong in saying this.

Thanks,

Siva.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi,

maybe this

Temp:
LOAD * Inline [
S.NO|Field|DataType
1|Field1,Field2,Field3|String,Num,Boolean
]
(delimiter is '|');

LOAD S.NO,SubField(Field,',',IterNo()) as Field,SubField(DataType,',',IterNo()) as Datatype
Resident Temp
While IterNo() <= 3;
Drop Table
Temp;

Regards,

Antonio

View solution in original post

9 Replies
el_aprendiz111
Specialist
Specialist

Hi,

LOAD *,
TEXT(SubField(FIELD,',',1))              AS FIELD_String,
NUM (SubField(FIELD,',',2))              AS FIELD_Num,
IF(SubField(FIELD,',',3),True(),False()) AS FIELD_Boolean;
;
LOAD * Inline
[
S.NO|FIELD
1|Field1,Field2,Field3
]
(delimiter is |);

antoniotiman
Master III
Master III

Hi,

maybe this

Temp:
LOAD * Inline [
S.NO|Field|DataType
1|Field1,Field2,Field3|String,Num,Boolean
]
(delimiter is '|');

LOAD S.NO,SubField(Field,',',IterNo()) as Field,SubField(DataType,',',IterNo()) as Datatype
Resident Temp
While IterNo() <= 3;
Drop Table
Temp;

Regards,

Antonio

MarcoWedel

one way to additionally make the number of subfields dynamic:

QlikCommunity_Thread_266496_Pic1.JPG

LOAD S.NO,

    SubField(Field,', ',IterNo()) as Field,

    SubField(Datatype,', ',IterNo()) as Datatype

INLINE [

    S.NO, Field, Datatype

    1, "Field1, Field2, Field3", "String, Num, Boolean"

    2, "Field4, Field5", "Integer, Double"

    3, Field6, Real

    4, "Field7, Field8, Field9, Field10", "Single, Char, Word, Byte"

] While IterNo()<=SubStringCount(Field,', ')+1;

hope this helps

regards

Marco

Anonymous
Not applicable
Author

Thanks for the reply, Antonio. But it's working only incase of inline load but not on data loading from excel sheet.

MarcoWedel

can you post a small sample of your excel (or some mockup data having the same sturcture) to demonstrate that this solution is not restricted to inline loads?

thanks

regards

Marco

antoniotiman
Master III
Master III

Why not ??!!

This is an example. according your data.

Change Inline to Excel File.

Anonymous
Not applicable
Author

Thanks for the reply, Marco.

I have provided sample excel data. Please help me with this data.

Anil_Babu_Samineni

Seems working to me. Aren't you?

Temp:

LOAD S.NO, Field, Datatype

FROM (ooxml, embedded labels, table is Sheet1);

Final:

NoConcatenate

LOAD S.NO,SubField(Field,',',IterNo()) as Field,SubField(Datatype,',',IterNo()) as Datatype

Resident Temp

While IterNo()<=SubStringCount(Field,',')+1;

DROP Table Temp;

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Thank you all so much guys. It's working.

Regards,

Siva.