DSXchange

Posted: **Tue Mar 20, 2007 8:29 am**

I am trying to read a file and skip few records based upon the following two columns. Pipe delimiter used between the two columns.
Column1|Column2
Property|CutOff
Target|11111
Min|9999
Max|10000
Comment|This is a test
Property|Weight
Target|222
Min|3434
Max|77777
UOM|mm
Comment|This is a test
Property|Temperature
Target|
Min|
Max|
UOM|
Property|Adhesive
Target|4
Min|3
Max|6
UOM|inch

Whenever I find a word "property" in column1, I have to check if column2 contains data in it till the next property word arrives in column1. If column2 does not contain any data for some property in column1 (like in case of the property called Temperature) then I have to skip that Property data and use the next Property data(which is adhesive). Can any one tell me how can I accomplish this challenging logic. I will appreciate any help.
Thanks

Posted: **Tue Mar 20, 2007 8:38 am**

Run in sequential mode and filter out all records that are empty in the second column. This will leave you with all records that have data in the second column including two or more adjacent rows with Property in the first column. Pass it through the transformer see if you have adjacent rows with same values in column 1. Skip all, except last.

Posted: **Tue Mar 20, 2007 10:51 am**

Just write a small script to do so.....

awk -F"|" '$1/Property/ {print $1, $2}' File_Name > OutputFilename

Should help you...

do let me know if you have any prob.....with this...

Posted: **Tue Mar 20, 2007 11:11 am**

Write a shell script

Code: Select all

awk -F"|" ' /Property/ { tmp=$0 ; getline; if($2 != "" ) print tmp } $2 != ""
 ' file

Or as DSGuru suggested do it in TX use contraint

Code: Select all

 NOT(ISNull(read.column1))

[/code]

Posted: **Tue Mar 20, 2007 11:46 am**

Both Sreedhar and Ganesh's code have produced same output
I am using this code

awk -F"|" ' /Property/ { tmp=$0 ; getline; if($2 != "" ) print tmp } $2 != "" ' Test5 > Test6

Correct me if I am wrong

Posted: **Tue Mar 20, 2007 12:08 pm**

Use that code as a filter command in your sequential file stage. You do not need to redirect the output to a file.

Posted: **Tue Mar 20, 2007 2:03 pm**

I used the same code to filter in the sequential file stage and I see still the data exhisting when I read the file. The UNIX code has something missing it in to Filter out according to my requirement.

awk -F"|" ' /Property/ { tmp=$0 ; getline; if($2 != "" ) print tmp } $2 != "" '

Posted: **Tue Mar 20, 2007 2:25 pm**

Have you tried the command from command prompt ??
Let me know if it gives desired result...

Posted: **Tue Mar 20, 2007 2:34 pm**

By the way how you filter in Seq file itself ??

Sorry I am a Rookie in PX...

Posted: **Tue Mar 20, 2007 2:50 pm**

I tried doing it on a putty and then as DSguru said typed the same logic in the Filter option for Sequential file and I get my exact file as out put.

Goto Options and use "Filter" and then type the UNIX code in there to get the data. When I read the data I dont see any change in the data as I have desired.

Posted: **Tue Mar 20, 2007 2:55 pm**

Ok but what you get if you run in putty ?

Do you see the changes ??

Redirect it to another file to check if its correct ...

Posted: **Tue Mar 20, 2007 3:00 pm**

Thats what I mean there are no changes to the file. Here is the code that I am using in Putty.

awk -F"|" ' /Property/ { tmp=$0 ; getline; if($2 != "" ) print tmp } $2 != "" ' Test5 > Test6

When I see Test6 file its exactly the same as Test5. No changes to the file. Infact I should have had four rows dropped out of my input data.

Posted: **Tue Mar 20, 2007 3:06 pm**

I have sent you a PM check it out and respond.

Try this in Shell script

and run it-

Code: Select all

#Shell Script name  ganesh.awk 
BEGIN {
  FS=OFS="|"
}

tolower($1) == "property" { _prop=$0 ; skip=0;next}
$2 == "" {skip++;next}
!skip { print _prop; skip++;next }
1

Run it using -

Code: Select all

nawk -f ganesh.awk myFile.txt

Posted: **Tue Mar 20, 2007 3:11 pm**

ganesh, your command works fine on command line, I tested it out too. The first awk command that you posted.
kashif, why does'nt the command work as a filter for you? Try it out on command line as others requested. Also make sure that the columns that are supposed to be empty are "really empty" and not have a space in it.

Posted: **Tue Mar 20, 2007 3:50 pm**

It works my friends.
Thank you so much actually I had space in the Data.
So that brings another question in my mind. Actually if I have space in the column and if I had to drop those then what do I do.

Thanks for your help guys. You guys are the best.

DSXchange

Strange Logic

Strange Logic