Setting a Character Set for Columns

ScottDun · Post by **ScottDun** » Thu Dec 17, 2015 8:15 am

Hi,

I want to set a certain character set to rows. To try and explain...

TABLENAME CLOB
PROV_TB A44224448
ADDRESS A44224448
ASSOC A0000000
TAXES A0000000
ENTY A0000123
PROV_TB A6541235

So I have 2 columns named TABLENAME and CLOB and the first row is PROV_TB and it has an ID of 44224448. The rows from PROV to the next PROV must have the same identifier (that is, PROV to ENTY must have 44224448). In the file I am trying to concoct a derivation in the transformer that will ensure that for each PROV_TB, the rows under it will get the ID of the PROV. So if there are 10 PROV_TB, there will be 10 identifiers and, for those rows following the PROV_TB, must match it.

To make it a little harder, I have assigned a track number (call it TN) starting at 5000 for each PROV_TB. So 10 PROV_TB is 5000 to 5009 and each of them has its own Identifier. How do I set the identifier to each row in between PROV_TBs so that they, and the TN, or the same?

chulett · Post by **chulett** » Thu Dec 17, 2015 9:00 am

It's not that different from the other conversations we've had on stage variables and group change detection. You would need a stage variable for this field and then set it to the current value when you hit the PROV_TB record and then 'set it to itself' i.e. leave it unchanged for every other row.

samyamkrishna · Post by **samyamkrishna** » Thu Dec 17, 2015 9:13 am

Yes like Craig mentioned.
check out this post

viewtopic.php?t=130373

ScottDun · Post by **ScottDun** » Thu Dec 17, 2015 9:22 am

Different conversion... I want to do a copy and paste kind of thing. I want to copy the PROV_TB ID (44224448) and paste it into the rows under it

chulett · Post by **chulett** » Thu Dec 17, 2015 9:26 am

Understood and no, it's not really different... just a variation on a theme. And my post explains how to do it. Stash it on the PROV_TB record, use it on all of the others.

Have you tried it, have any specific questions?

ScottDun · Post by **ScottDun** » Thu Dec 17, 2015 9:40 am

My company still hasn't received the Premium membership as of yet. All I can see is up to "...Have You tried, have an..."

chulett · Post by **chulett** » Thu Dec 17, 2015 10:21 am

Once more into the breach...

ScottDun · Post by **ScottDun** » Thu Dec 17, 2015 10:52 am

When you say stash it on the PROV_TB record... How do i go about doing this? To give a little more insight, I have a job with the text file, a column import, 2 transformers and the final dataset (which I will use for the next job). In the column import, I am taking out the TABLENAME & CLOB, and the 7 other columns I am creating from the CLOB. The 1 st transformer I am simply just mapping it through to the columns. The second transformer is where I add 7 stage variables to create the TN (tracking number). Then to the dataset.

So originally I used CLOB [2,8] to get the ID but it doesn't work because of the sample I showed you. I want the CLOB [2,8] for the PROV_TB and "copy n paste" it onto the rows under it.

As for your post, I do not know how to stash it on the PROV_TB

chulett · Post by **chulett** » Thu Dec 17, 2015 11:21 am

I meant you 'stash' it on encountering the PROV_TB record as that's when you know what it needs to be on the following records. So basically a stage variable that is set when you read that row and left unchanged for the others. Something along the lines of:

Code: Select all

svCurrID: if TABLENAME = 'PROV_TB' then CLOB[2,8] else svCurrID

You can then assign that stage variable to the records.

ScottDun · Post by **ScottDun** » Thu Dec 17, 2015 12:40 pm

I input this sv and named it "PID". I set the derivation as above. I drag that derivation into the PID column that is created and the output is weird. The PIDs go well with the PROV_TB, they match the 8 characters. And then the rest of the rows do not. The column is a mess. If PROV_TB is 44224448 and TN is 5000, the next row under it says ADDRESS with 44224448 with TN of 5001, ADDR 45612358 TN 5001. The TN is supposed to remain the same for each ID BUT it can be fixed if the proper Identifier falls in place. I do not know why the address of 45612358 is turning into a 44224448. Is there a sort I can do?

samyamkrishna · Post by **samyamkrishna** » Thu Dec 17, 2015 2:14 pm

For this logic to work the records have to be read into the transformer in the same order that you have shown in your initial post.

ScottDun · Post by **ScottDun** » Thu Dec 17, 2015 3:15 pm

I used Same partition in the first transformer, hooked that up to a dataset and the columns are all varied. Is there a better way for this?

samyamkrishna · Post by **samyamkrishna** » Thu Dec 17, 2015 3:28 pm

Do not do any partitioning any where till this logic is executed.
Just run this job on a single node APT config file.

chulett · Post by **chulett** » Thu Dec 17, 2015 3:29 pm

Run the job on a single node, see if that 'solves' your problem.

ScottDun · Post by **ScottDun** » Fri Dec 18, 2015 12:29 pm

How can I make the file coming in read exactly that way throughout the entire job? I have the file and I am bringing it to a transformer and then to another transformer and, lastly, the dataset. If input file reads PROV, ADDRESS, ASSOC, ENTY, PROV, how can I keep the exact formation of the file?