Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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 |);
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
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
Thanks for the reply, Antonio. But it's working only incase of inline load but not on data loading from excel sheet.
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
Why not ??!!
This is an example. according your data.
Change Inline to Excel File.
Thanks for the reply, Marco.
I have provided sample excel data. Please help me with this data.
Seems working to me. Aren't you?
Temp:
LOAD S.NO, Field, Datatype
FROM
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;
Thank you all so much guys. It's working.
Regards,
Siva.