I have a user variables activity in a job sequence. I have defined 12 user variables within it and one example is below. Each variable pulls the record count from a different text file. The text files each contain one line with the record count from an extract. The sample text looks like this: --Row Count:000000000000123. I have created the user variable below to pull the count from the file and suppress the zeros. The problem I'm having is I can't figure out how to display a single 0 if the actual count is 00000000000000. It displays nothing in those instances. I've tried multiple IF-THEN-ELSE statements and none of them worked. I originally tried to do a TRIM("`cut -f 2 -d ':' file_rec_ct1.txt`",'0','L') to remove the leading zeros and that wouldn't work. I haven't been able to get any DS functions to work with the result of the UNIX command. There doesn't seem to be much info out there on the user variables activity and using UNIX commands. Any help or suggestions would be greatly appreciated.
"`cut -f 2 -d ':' file_rec_ct1.txt | sed 's/^0*//'`"
User Variables Activity - How to use UNIX commands
Moderators: chulett, rschirm, roy
Would I need to do this comparison in another user variable activity? I don't believe you can reference another user variable within the same user variable activity.
I've tried the below syntax in the user variable stage but it would never display a 0 when the record count was 0.
If "`cut -f 2 -d ':' file_rec_ct1.txt`" = '000000000000000' Then '0' Else "`cut -f 2 -d ':' file_rec_ct1.txt | sed 's/^0*//'`"
If "`cut -f 2 -d ':' file_rec_ct1.txt`" = 0 Then 0 Else "`cut -f 2 -d ':' file_rec_ct1.txt | sed 's/^0*//'`"
I've tried the below syntax in the user variable stage but it would never display a 0 when the record count was 0.
If "`cut -f 2 -d ':' file_rec_ct1.txt`" = '000000000000000' Then '0' Else "`cut -f 2 -d ':' file_rec_ct1.txt | sed 's/^0*//'`"
If "`cut -f 2 -d ':' file_rec_ct1.txt`" = 0 Then 0 Else "`cut -f 2 -d ':' file_rec_ct1.txt | sed 's/^0*//'`"
count="`cut -f 2 -d ':' file_rec_ct1.txt | sed 's/^0*//'`"
if [[ "$count" == ""]]; then
$count="0";
fi
something like that, then just use $count later on in your script.
I am assuming that you were assigning the result of that cut+sed to a variable to begin with.
(code may not be syntactically correct, but you get the drift of it.)
if [[ "$count" == ""]]; then
$count="0";
fi
something like that, then just use $count later on in your script.
I am assuming that you were assigning the result of that cut+sed to a variable to begin with.
(code may not be syntactically correct, but you get the drift of it.)
Below is what I have in my User Variable Activity.
File1_Rec_Ct: "`cut -f 2 -d ':' file_rec_ct1.txt | sed 's/^0*//'`"
File2_Rec_Ct: "`cut -f 2 -d ':' file_rec_ct2.txt | sed 's/^0*//'`"
File3_Rec_Ct: "`cut -f 2 -d ':' file_rec_ct3.txt | sed 's/^0*//'`"
and so on....for a total of 12 variables.
I use these variables in a routine activity (DSSendMailTester) which sends out an email with the file counts. Are you saying I should create another User Variables Activity (or something else) and do the IF/THEN/ELSE logic to check for 0?
File1_Rec_Ct: "`cut -f 2 -d ':' file_rec_ct1.txt | sed 's/^0*//'`"
File2_Rec_Ct: "`cut -f 2 -d ':' file_rec_ct2.txt | sed 's/^0*//'`"
File3_Rec_Ct: "`cut -f 2 -d ':' file_rec_ct3.txt | sed 's/^0*//'`"
and so on....for a total of 12 variables.
I use these variables in a routine activity (DSSendMailTester) which sends out an email with the file counts. Are you saying I should create another User Variables Activity (or something else) and do the IF/THEN/ELSE logic to check for 0?
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
@PaulVL
Coding on a user variable activity will be DataStage basic language, ""+ 0 will be zero... Empty string plus zero will be zero
@Block
Results from UNIX commands contain field markers (@FM, or Char(254)) that would need to be removed before you could actually do other actions. A convert function is ideal to replace them with empty string
In the user variable do the following
RecordCount = If Len(Trim(Convert(@FM,'',YourUnixCommandremoveZeroes)) = 0 Then 0 Else ....
Try and let us know how it goes
Coding on a user variable activity will be DataStage basic language, ""+ 0 will be zero... Empty string plus zero will be zero
@Block
Results from UNIX commands contain field markers (@FM, or Char(254)) that would need to be removed before you could actually do other actions. A convert function is ideal to replace them with empty string
In the user variable do the following
RecordCount = If Len(Trim(Convert(@FM,'',YourUnixCommandremoveZeroes)) = 0 Then 0 Else ....
Try and let us know how it goes
Julio Rodriguez
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
Thanks for the possible solution. I tried this but couldn't get it to work correctly. I still couldn't get it to display a 0.
I ended up going in a completely different direction and was able to get it to work.
The new approach was this: cut -f 2 -d ':' file_rec_ct1.txt | gawk '{printf("%'"'"'d\n", $1)}'
This removes the zeros, adds commas and will display a 0 if the count is 0.
I ended up going in a completely different direction and was able to get it to work.
The new approach was this: cut -f 2 -d ':' file_rec_ct1.txt | gawk '{printf("%'"'"'d\n", $1)}'
This removes the zeros, adds commas and will display a 0 if the count is 0.