Load Stat in one table based on Parameters in another

[Anonymous]

I need to load a stat of 1 or 0 to a Resident table based on whether or not certain parameters are met in another table

I need to load into the Header table whether or not it has an Addon item. In order for their to be an add on item per ticket it must have the primary product and a secondary product.  The primary class is the 001 and the secondary class is the 070.  Header and Detail are loaded prior to the following. All records qualifed other than the Keys.

Header:

Load *,

    If(Count(Detail.ClassMMSCodeNumeric = '001')>0 and Count(Detail.ClassMMSCodeNumeric = '070')>0,1,0) as Addon

    Resident Header;

I get the error of Detail.Class.... can't be found.  I do not want to innerjoin the records because then I'd have thousands of header records more then I need which would make calculations from the header record moot without forcing Distinct calculations into my charts.  Is this possible?

This will be used to gauge up-selling.

[Anonymous]

A possible solution could be to load distinct keys from the Detail table while checking the parameters laid out.

I am getting an invalid expression error when trying to do this.

Invalid expression

Addon:

Load

Distinct %_CHECKKEY,

If(Count(Detail.ClassMMSCodeNumeric = '001')>0 and Count(Detail.ClassMMSCodeNumeric = '070')>0,1,0) as Addon

Resident Detail

Since each header row would have normally >1 detail row how can I consolidate the detail rows into 1 record saying True of False? I realize the load is taking one record at a time. Is it possible to evaluate several records, then insert 1 record into my table?

[Anonymous · Accepted solution]

What I have tried is to create a Num(true/false) field in the initial load of the detail portion of the tickets. Then from a resident table load I've pulled the "max" of that field. Taking the max only will return 1 if there any of the records are true and 0 if all are false.

P_Addon:

LOAD

    %_CHECKKEY,

     Max(Detail.Primary) as P_Addon

Resident Detail

Group by %_CHECKKEY;

S_Addon:

LOAD

    %_CHECKKEY,

    Max(Detail.Secondary) as S_Addon

Resident Detail

Group by %_CHECKKEY;

From here I did a left join to insert the true false fields into my ticket Header records. So now I can create a chart for header records to see if both parameters are True. If both are true I can do a count of tickets that have an Add On item to get my percentage of up-selling. I then drop the additional tables created with the above syntax.

Checkkey = Store# & Ticket# & Datevalue

True/False fields in the initial Detail records load:

Num(If(ClassMMSCodeNumeric = '001',1,0))as Primary,

Num(If(ClassMMSCodeNumeric = '070' or ClassMMSCodeNumeric ='010' or ClassMMSCodeNumeric ='080',1,0))as Secondary,