Analyze data separately within same field? 1

[antiskeptic]

Hopefully this is a quick question....

The field I need to analyze has data that looks similar
to this:

Accounting\NSF\2000 Logs\Jan

Basically, as you can probably guess, this is
a folder path. And I need to isolate the individual folders.

What I am trying to do, is group another field (the user's name) by the "common denominator" folder.

FOr example:
Accounting\ JDoe Owner
Accounting\NSF\ JDoe Owner
Accounting\NSF\2000 Logs\ JDoe Owner
Accounting\NSF\2000 Logs\Jan JDoe Owner
Accounting\NSF\2000 Logs\Feb JDoe Owner
Accounting\NSF\2000 Logs\Mar JDoe Owner

All I care about seeing, in my finished query, is the top line...because we know that JDoe owns the topmost folder, so he obviously owns all folders inside it. ( This is not exactly true in the real world...but it's good enough to get my point across, I think. ) Unless of course his owner status changes somwhere inside the main folder.

Example:
Accounting\ JDoe Owner
Accounting\NSF\ JDoe Owner
Accounting\NSF\2000 Logs\ JDoe Owner
Accounting\NSF\2000 Logs\Jan JDoe Owner
Accounting\NSF\2000 Logs\Feb JDoe Owner
Accounting\NSF\2000 Logs\Mar JDoe Owner
Accounting\NSF\2000 Logs\Qtr1 JDoe ReadOnly

I hope this makes sense...and if not please post any questions that will help you to help me.
I found a featur in Crystal that I'm not sure quite how to use it...Split() But this gives an array and needs to be "subscripted"? really confusing...but maybe will help in my plight.

Thanks a million in advance.
Sincerely,
Antiskeptic

 

[synapsevampire]

You're definitely on the right track with SPLIT(), however Join makes more sense here (it's counterpart).

To get the first value of the folder you can use either:

join({table.field},"\")[1]

or

left({table.field},instr({table.field},"\")-1)

Demonstrating technical information is generally best performed by example:

Crystal version
Database/connectivity used
Example data (you did)
Expected output

You mention unless his owner status changes, but not what happens as a result.

-k

 

[lbass]

I think you could just use:

left({table.string},instr({table.string},"/"))

To take account of changing status, you could group by the following formula:

left({table.string},instr({table.string},"/")) + {table.status}

-LB

 

[antiskeptic]

okay..sorry I didn't cover it all...let's try again. But please bear with me...this is incredibly confusing, and I'm not really sure how to explain it on a post...please have patience. *ha* And "Owner" was a bad example...this is a permissions list (Users have different permissions to different folders) so I'll go with that this time.

Accounting\ JDoe Write&Read
Accounting\NSF\ JDoe All
Accounting\NSF\2000 Logs\ JDoe All
Accounting\NSF\2000 Logs\Jan JDoe All
Accounting\NSF\2000 Logs\Feb JDoe All
Accounting\NSF\2000 Logs\Mar JDoe All
Accounting\NSF\2000 Logs\Qtr1 JDoe ReadOnly
Accounting\ LPat All
Accounting\NSF\ LPat All
Accounting\NSF\2000 Logs\ LPAT All
Accounting\NSF\2000 Logs\Jan LPAT All
Accounting\NSF\2000 Logs\Feb LPAT All
Accounting\NSF\2000 Logs\Mar LPAT All
Accounting\NSF\2000 Logs\Qtr1 LPAT ReadOnly
Accounting\ TCar Write&Read
Accounting\XYS\ TCar Write&Read
Accounting\XYS\2002 Logs\ TCar Write&Read
Accounting\XYS\2002 Logs\Jan TCar ReadOnly
Accounting\XYS\2002 Logs\Feb TCar All
Accounting\XYS\2002 Logs\Mar TCar All
Accounting\XYS\2002 Logs\Qtr1 TCar ReadOnly

so the output I would want to see from this list is only this: (The "common denominators")


Accounting\
LPat All
JDoe Write&Read
TCar Write&Read
Accounting\NSF\
LPat All
JDoe All

(don't need to see all the folders below the lowest common level, because it's all the same permissions in all the lower levels...only if one changes below in the "tree" do I need to see it)

Accounting\NSF\2000 Logs\Qtr1
LPat ReadOnly
JDoe ReadOnly
Accounting\XYS\
TCar Write&Read
Accounting\XYS\2002 Logs\Jan
TCar ReadOnly
Accounting\XYS\2002 Logs\Feb
TCar All
Accounting\XYS\2002 Logs\Qtr1
TCar ReadOnly

I'm working with Crystal9 and am pulling this out of an access db...but the original list is just a csv that I turned into a table so I could manipulate it in Crystal.
Does any of this make any sense? ANd how can I use the join / split functions to do this? Can I find out how many "pieces" to the "split array"? (in other words how many folders are in that particular field?)
Example:
Accounting\XYS\2002 Logs\Qtr1 has 4
Accounting\XYS\ only has 2

how can I make it keep checking for common denominators when each field keeps getting deeper? Is there any sense being made here. *ha* 'Cause I think I've even lost my train of thought now. HELP!!! *ack*

Antiskeptic

 

[lbass]

It looks to me like you can just group on {table.string} to get the desired results--I don't think you need or should break out the different levels for what you're trying to do.

-LB

 

[antiskeptic]

unfortunately it is not that simple, unless you know of some trick for hiding all the other folders that are just repeating the same information. If I simply group on the {table.string} as suggested...this is the result:

Accounting\
JDoe Write&Read
LPat All
TCar Write&Read

Accounting\NSF\
JDoe All
LPat All
Accounting\NSF\2000 Logs\
JDoe All
LPat All
Accounting\NSF\2000 Logs\Jan
JDoe All
LPat All
Accounting\NSF\2000 Logs\Feb
JDoe All
LPat All
Accounting\NSF\2000 Logs\Mar
JDoe All
LPat All
Accounting\NSF\2000 Logs\Qtr1
JDoe ReadOnly
LPAT ReadOnly

Accounting\XYS\
TCar Write&Read
Accounting\XYS\2002 Logs\
TCar Write&Read
Accounting\XYS\2002 Logs\Jan
TCar ReadOnly
Accounting\XYS\2002 Logs\Feb
TCar All
Accounting\XYS\2002 Logs\Mar
TCar All
Accounting\XYS\2002 Logs\Qtr1
TCar ReadOnly

When really all I want to see is:

Accounting\
LPat All
JDoe Write&Read
TCar Write&Read
Accounting\NSF\
LPat All
JDoe All
Accounting\NSF\2000 Logs\Qtr1
LPat ReadOnly
JDoe ReadOnly
Accounting\XYS\
TCar Write&Read
Accounting\XYS\2002 Logs\Jan
TCar ReadOnly
Accounting\XYS\2002 Logs\Feb
TCar All
Accounting\XYS\2002 Logs\Qtr1
TCar ReadOnly

I know it seems like a minimal differnce. But when you are talking about thousands of users-- and someone who has to go through this list to determine which users are...no longer valid users / permissions have been changed / user groups that are involved -- it's going to make a big difference. You see my dilema. *ugh*
I still think Split / Join, may be my best bet, but I just don't know quite how to go about it, or what would be the most efficient use of them.

Antiskeptic

 

[antiskeptic]

also can anyone answer this question?

Can I find out how many "pieces" to the "split array"? (in other words how many folders are in that particular field?)

Example:
Accounting\XYS\2002 Logs\Qtr1 has 4
Accounting\XYS\ only has 2

the reason I ask is so I can try this....

split({Testing.Field1},'\')[4]

but I keep getting an error:

"A subscript must be between 1 and the size of the array"

So for some of them...like:

Accounting\XYS\2002 Logs\Qtr1

it will work...but this :

Accounting\XYS\

will produce that error. See what I mean? I thought maybe I could group on each layer/folder...but I could be going in the wrong direction with this.
Am I just confusing everyone more? Am I making this way harder than it should be??
Something tells me the answer is right in front of me.

Antiskeptic

 

[lbass]

You can do something like:

if ubound(split({testing.field1},"/")) >= 4 then
split({testing.field1},"/")[4]

...changing the number for each formula.

What version of CR are you using?

-LB

 

[antiskeptic]

Version I am using is Crystal 9.0 and working with an Access table.

 

[antiskeptic]

okay another attempt...

Local NumberVar Array x :=
makearray((split({Testing.Field1},"\")))

but I keep getting an error that tells me the array needs to be subscripted...???? What does it mean?

Antiskeptic

 

[lbass]

Have you thought about using a crosstab? If you used {table.string} as the column, user as the row, and permission status as the summary (maximum or NthMostFrequent), you would still have the redundancy, but the crosstab would provide a simple format for readability. You could rotate the column headings so that they fit easily on one page width.

-LB

 

[antiskeptic]

Unfortuneatly the redundancy is a big sticky point here. They are refusing to accept the redundancy...zero tolerance. *ha* ...too many records involved to allow redundancy. A difference of 5000+ pages as opposed to <1000 pages. Any ideas out there?

 

[antiskeptic]

And lbass,
I want you to know...that although it seems like I'm shooting down all your ideas...I am not. I try them, and they just have not been what I need for this....my bosses are being very picky. But I appreciate all your attempts and help so far...at least it's making me think.
and I'm learning a lot.

Thank you!!

Antiskeptic

 

[lbass]

You could try the following. First group on {table.filename}. Then create a formula {@distconcat}:

whileprintingrecords;
numbervar x;
stringvar y ;

if instr(y,{table.name}+{table.permissionstatus}) = 0 then
(
y := y + {table.name}+{table.permissionstatus};
x := x + 1
);

Then go to the section expert->details->suppress->x+2 and enter:

{@distconcat} = 0

This should suppress subsequent instances of the same person/status across groups.

-LB

 

[synapsevampire]

Note that my original post requested technical information that you ignored.

You're getting close to supplying this now, and that will likely result in the solution you seek.

-k

 

[antiskeptic]

Synapsevampire...I'm sure if you had read my very next post you would have seen that I DID provide you with the information you requested. What did I NOT provide??!!

My next post stated the following:
"
okay..sorry I didn't cover it all...let's try again. But please bear with me...this is incredibly confusing, and I'm not really sure how to explain it on a post...please have patience. *ha* And "Owner" was a bad example...this is a permissions list (Users have different permissions to different folders) so I'll go with that this time..."
~~~~~~my examples included permissions from here on~~~~~
"I'm working with Crystal9 and am pulling this out of an access db...but the original list is just a csv that I turned into a table so I could manipulate it in Crystal.
"

Then of course I layed out my sample data and an example of the output I wanted.
But maybe you wanted it laid out more structured...like this?

Crystal version - 9 (I posted this a couple times)

Database/connectivity used - Access DB (ok...I didn't put that I am using an ADO connection...but that is just what I happen to be using now...I am not limited to this)

Example data (you did) (yes)

Expected output (Laid this out too)

So I honestly don't know why you felt the need to write to me like I had completely ignored you. I need all the help I can get and did just what you said so I could get as much feed back as possible. So I don't know what else you need, that I didn't provide???

and LBass...I'm going to give this a try and I'll let you know...Thank you.

Antiskeptic

 

[antiskeptic]

Oh WOW LBASS!!! That did it...! *woohoo*

But there just one more minor problem...But I think I can get around it fairly easy. *fingers crossed*
But I wanted to get your thoughts too.
you wrote:
~~"This should suppress subsequent instances of the same person/status across groups." ~~

This is correct, it did just that. Only problem is that it even goes across the top most group (the base folder).

I.E.:

Acct\
Home\
Accounting\
HR\

etc...

Is there any way to get around this? Can I reset the variable at a different level maybe? The thing is, each "base folder" list needs to go to different people. SO the names(users on the list) have to be at least once on each "base folder" list. Does this make any sense?

But otherwise, I think this is exactly what I need...You are a genius! *wink*

Sincerely,
Antiskeptic

 

[lbass]

I think you can just change the suppression formula to:

len(trim({table.string})) > instr({table.string},"/") and
{@distconcat} = 0

-LB

 

[antiskeptic]

As I said...you are a genius, lbass! That is exactly what it needed. I was thinking so narrowly, that didn't even occur to me. *ha*

You've helped me to think "outside the box" on this one...and I've learned a few more lessons in this problem. Thank you so, so, much!

Sincerely,
Antiskeptic

 

[synapsevampire]

Sorry, antiskeptic, I saw LB ask for CR version thinking that you hadn't posted it. I tend to bow out of threads hen multiple people are responding.

Per usual, LB handled this nicely.

-k