topic Re: transpose the column and have separate entries of data in App Development

transpose the column and have separate entries of data

rushi1093 — Fri, 15 Nov 2024 21:22:16 GMT

cc	13	14	15	16	17	18
BPIPE6182215103	22	kyUSBI31F_SMKL_Curncy	44	kyUSBI63F_SMKL_Curncy	22	kyUSFOSC1_SMKL_Curncy

above is the data snippet,

Column cc is just a concatenation of column 1 to column 12. (not imp here)

column 13, 15, 17 etc. are the counts

column 14, 16, 18 etc. are the codes.

My requirement is having separate entries of column 13,15,17, and 14, 16, 18 against the identifier mentioned in column cc.

following is the expected output: look for columns count and code at the end..

cc	Count	Code
BPIPE6182215103	22	kyUSBI31F_SMKL_Curncy
BPIPE6182215103	44	kyUSBI63F_SMKL_Curncy
BPIPE6182215103	22	kyUSFOSC1_SMKL_Curncy

Basically I should get distinct entry of identifiers (column 1 to 12) for their respective code and count. Column 13, 15, 17 which have count should be vertically populated instead of horizontal. same for column 14, 16, 18

Kindly assist.

Re: transpose the column and have separate entries of data

jonashertz — Mon, 02 Oct 2023 10:02:20 GMT

If you have many or unknown number of value columns then Crosstable is the best function to use. However in this case, if you do not have too many columns and the number of columns is fixed and reasonably small, this subfield trick might be easier to use:

t1:

Load * Inline [

cc, f13, f14, f15, f16, f17, f18,

BPIPE6182215103, 22, kyUSBI31F_SMKL_Curncy, 44, kyUSBI63F_SMKL_Curncy, 22, kyUSFOSC1_SMKL_Curncy

];

t2:

Load

cc,

SubField(CountCode,'|' ,1) as Count,

SubField(CountCode,'|' ,2) as Code;

Load

cc,

subfield(f13 & '|' & f14 & '/' & f15 & '|' & f16 & '/' & f17 & '|' & f18, '/') as CountCode

Resident t1;

drop table t1;

Re: transpose the column and have separate entries of data

rushi1093 — Tue, 03 Oct 2023 08:01:36 GMT

Since I have large and fluctuating number of columns, I am trying to avoid use of subfield...My end goal is to automate the script in a way that will identify every number after RS-row separator as Count and every string after GS-group separator as Code.