advanced set analysis for exclusion

[shruthibk]

Hi how to write advanced set analysis expression to exclude values from another table,

my requirement is like

tab1:

id,

stid,

tab2:

id,

rid,

caspid,

review

]

where i have find count of caspid  where review='progress'

and id in  tab2 should not be in tab1

thanks,

shruthi

[sunny_talwar]

Try like this:

=Count({<review = {'progress'}, id = {"=NullCount(Key) > 0"}>}caspid)

Where Key is a field created in tab1 like this:

RowNo() as Key

Here is a sample for you to look at

Table1:

LOAD *,

  RowNo() as Key;

LOAD * Inline [

id, stid

1, 4

2, 5

];

Table2:

LOAD * Inline [

id, rid, caspid, review

1, 3, 5, progress

2, 2, 4, none

1, 2, 4, non

3, 2, 3, progress

];

[swuehl]

Another option without changing the data model could be

=Count({<review = {'progress'}, id = e({<stid={"*"}>}) >} caspid)

[sunny_talwar]

The only concern I would had with this is if stid is null in the first table for some reason. That's why I thought that it would make sense to create a field in the script which I know won't be null.

Remember, we need to resolve the issue for good so that we don't have to come back to it in the future

[swuehl]

Right, stid should have a value whereever id has a value in table 1.

If this is not a case, we need to create a field in the script.

[sunny_talwar]

Yay!!! its feel good to be right once in a while. But Stefan, let me take this as an opportunity to thank you for the amount of learning I have been able to do just by looking at your responses. Things I would have never been able to learn.

Best,

Sunny

[swuehl]

Hi Sunny,

you are understating your part completely! I don't think someone can teach you a lot anymore.

And do you even sleep?

Best,

Stefan

[sunny_talwar]

I do, but the number of hours I sleep had drastically gone down due to a totally unrelated reason. I am hoping this less sleep to fix in 2-3 months

[#9]

Also may be like this

tab1

id,

stid

from<path>;

tab2

id,

rid,

caspid,

review

from<path>

where not exists(id);

select table with expression

=count({<review={'progress'}>}caspid)