Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can we get output table like this from input table.
Hi.
You can use subfield function. Something like this:
Result:
Load *,
Subfield(Tmp, '#', 2) as value;
Load
Subfield(Column, ',', 1) as abcd-xyz,
Subfield(Column, ',', 2) as Tmp,
Subfield(Column, ',', 3) as status
Resident sourceTable;
Hi,
As per the above suggestion,its giving only the column information but i need the output table format.
Can you please help me on this.
TmpTable:
load
*
, subfield(headers,',',1) as headerOne
, subfield(headers,',',2) as headerTwo
, subfield(values, ',', 1) as valueOne
, subfield(values, ',', 2) as valueTwo
;
load
Column
, subfield(Column,'#',1) as headers
, subfield(Column,'#',2) as values
;
load * inline [
Column
abcd-xyz,STATUS#67,Delivered
abcd-xyz,STATUS#42,OtherStatus
](delimiter is ';');
let headerOne = peek('headerOne',0,'TmpTable');
let headerTwo = peek('headerTwo',0,'TmpTable');
Result:
load
valueOne as [$(headerOne)]
, valueTwo as [$(headerTwo)]
Resident
TmpTable
;
Drop table TmpTable;
You can check something like this one. But, you've mentioned only one row as example so i cannot determine that the column name will be correct for other rows.
Hi Thanks for the explanation.
but
You have given static values and dynamically i need
other records can contain many columns and values
and finally as per the output table multiple header with corresponding values
Hi.
Ok, this is better example.
TmpTable:
load
*
;
load
Column
, subfield(Column,'#',1) as headers
, subfield(Column,'#',2) as values
;
load * inline [
Column
abcd-xyz,STATUS#67,Delivered
sdsd-sds,wew-rere,status#55,null,cancelled
](delimiter is ';');
let rows = NoOfRows('TmpTable')-1;
Res:
load * inline [colToDrop];
for i =0 to $(rows)
let headers = lower(peek('headers', $(i), 'TmpTable'));
let values = lower(peek('values', $(i), 'TmpTable'));
Concatenate(Res)
load * inline [
$(headers)
$(values)
] (delimiter is ',');
next i;
drop field colToDrop;
drop table TmpTable;