Doing a if statement for a group of records

[#1]

Hi

I've got a data model with more than one table, but the data from my one table is duplicated when it links to some of the other tables.

I would actually now like to do an if statement, but for a unique value from the one table, based on the data from the other table which is causing the data to be duplicated.

For example, my dataset after the associations occurred looks something like this:

Deal Number Sales Franchise Device Type Scan Franchise Availability
5208666 222 Blackberry 222 1
5208666 222 Nokia 5929 1
4069994 323 HTC 222 2
4069994 323 Apple 222 2
4069994 323 Nokia 323 1
5048417 5929 Nokia 5929 1
5048417 5929 Blackberry 222 2
5048417 5929 Blackberry 323 2
5048417 5929 Charger 5929 1

From the data you can see that the deal number has duplicated and device type, Scan franchise and availability differs.

The rules around availability is that if Sales Franchise and Scan Franchise is the same, then availability is 1, but else availability will be 2.

I would now like to do a graph to do the following.

If availability for any of the lines of a unique deal = 2, then create a new field, called Stock Availability and set it to 'No'.
So, you can do a max on availability in your if, and if the max is 2, then 'No', else 'Yes'.

But I am not sure how you do it to check for all the lines of a specific deal?
For example: When looking at deal number 5048417, the max of availability is 2, so the new field will be 'No', but for deal 5208666, the max will be 1, so the new field will be set to 'Yes'.
What do I need to add to my if to state that it should be for when grouping the same deal number?

Hope I am making sense.

[#2]

Is there any other condition for calculating "avilability"?
If you are giving the condition like sales Fanchise equals with scan Franchise then 1 or 2,the result will be like this.So NP 🙂

Deal Number	Device Type	Sales Franchise	Scan Franchise	Availability
4069994	Nokia	323	323	1
4069994	Apple	323	222	2
4069994	HTC	323	222	2
5048417	Charger	5929	5929	1
5048417	Nokia	5929	5929	1
5048417	Blackberry	5929	222	2
5048417	Blackberry	5929	323	2
5208666	Blackberry	222	222	1
5208666	Nokia	222	5929	2

[#3]

Hi

Yes, there is more to this problem. If you take away device type, scan franchise and sales franchise in the data you will have deal number and availability left.

I would then like to check for the same deal, what is the max of the availability and create a new field to state that if the max=2, then stock was not available for this deal, but if max of availability for a deal is 1, then the stock was available.

[#4]

Hi, Please check the QVW, that i sent,hope that is useful.

[#5]

Hi

Thank you for trying to assist, but this is not what I need. The problem I have isn't how I need to determine what is available and not available. I've done that already in my base data.

The problems is that for the same deal I will have availability set to 1 and 2, based on my rule.

I now want to use this to say:

If(max(availability) = 2,"Stock Available","Stock Unavailable"), but because my deal numbers are duplicated in the data, I have to try and group my deal number.

Not sure if I should create a seperate table for this or if I can do it in a preceding load.

Please assist.

Regards

[#6]

To my understanding,

you want to create a new field in the Scripting Level i guess.For that you need to maintain a seperate table and it should be group by Deal Number.This Script may help you.

Data_Issue:
Load
*,
If(trim([Sales Franchise])=trim([Scan Franchise]),1,2) as [Availability];
LOAD
[Deal Number],
[Sales Franchise],
[Device Type],
[Scan Franchise]

FROM
Dataissue.xls
(biff, embedded labels, table is Sheet2$);

Data_Issue1:
Load
[Deal Number],
if(Max([Availability])=1,'Stock available','No Stock') as [Stock Avilability]
resident Data_Issue
group by [Deal Number];