SubField Function

[Anonymous]

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.

[el_aprendiz111]

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 · Accepted solution]

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:

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]

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]

Why not ??!!

This is an example. according your data.

Change Inline to Excel File.

[Anonymous]

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;

[Anonymous]

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

Regards,

Siva.