topic Re: SubField Function in QlikView

SubField Function

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Guys,

I have the sample data below,

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.

Re: SubField Function

el_aprendiz111 — Tue, 04 Jul 2017 20:06:05 GMT

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 |);

Re: SubField Function

antoniotiman — Tue, 04 Jul 2017 21:03:08 GMT

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

Re: SubField Function

MarcoWedel — Tue, 04 Jul 2017 21:42:37 GMT

one way to additionally make the number of subfields dynamic:

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

Re: SubField Function

Anonymous — Tue, 04 Jul 2017 21:47:28 GMT

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

Re: SubField Function

MarcoWedel — Tue, 04 Jul 2017 21:56:38 GMT

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

Re: SubField Function

antoniotiman — Tue, 04 Jul 2017 22:06:57 GMT

Why not ??!!

This is an example. according your data.

Change Inline to Excel File.

Re: SubField Function

Anonymous — Wed, 05 Jul 2017 12:14:21 GMT

Thanks for the reply, Marco.

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

Re: SubField Function

Anil_Babu_Samineni — Wed, 05 Jul 2017 13:01:46 GMT

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;

Re: SubField Function

Anonymous — Wed, 05 Jul 2017 13:19:56 GMT

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

Regards,

Siva.