Working with the statements QUALIFY and UNQUALIFY

[#1]

Hello everyone,

I´m trying to connect two tables by the field Month and Year, using the statements QUALIFY and UNQUALIFY.

See below the script:

Table1:

QUALIFY *;

Restaurant:

LOAD Category,

     Month(Mês) as Month,

     Year(Ano) as Year,

     Consume,

     [Avg/day],

     [Payed Value]

FROM  abc;

Table2:

LOAD Month(Mês) as Month,

     Year(Ano) as Year,

     [Recuperation Grill],

     [Recuperation paper],

     [Recuperation coffee],

     [Total Recuperation]

FROM abc;

UNQUALIFY Month, Year;

The idea is to create only one filter for Month and Year, creating a relationsheep between both tables.

The script is running with no errors, but there are no relationship, Month and Year are not recognized as same fields.

Anyone could help me to solve that?

Regards,

[sunny_talwar]

Try this:

QUALIFY *;
UNQUALIFY Month, Year;

Table1:
LOAD Category, 
     Month(Mês) as Month, 
     Year(Ano) as Year,
     Consume, 
     [Avg/day], 
     [Payed Value]
FROM  abc;

Table2:
LOAD Month(Mês) as Month, 
     Year(Ano) as Year, 
     [Recuperation Grill], 
     [Recuperation paper], 
     [Recuperation coffee], 
     [Total Recuperation]
FROM abc;

UNQUALIFY *;

[lironbaram]

Hi

change to this script

Qualify *;

UnQualify Month,Year;

Table1:

LOAD Category,

     Month(Mês) as Month,

     Year(Ano) as Year,

     Consume,

     [Avg/day],

     [Payed Value]

FROM  abc;

Table2:

LOAD Month(Mês) as Month,

     Year(Ano) as Year,

     [Recuperation Grill],

     [Recuperation paper],

     [Recuperation coffee],

     [Total Recuperation]

FROM abc;

UNQUALIFY *;

[#4]

Doint this i have a Synthetic Key as a result...

I am trying to do avoiding Synthetic Keys.

Thanks

[#5]

Doint this i have a Synthetic Key as a result...

I am trying to do avoiding Synthetic Keys.

Thanks

[sunny_talwar]

Try this in that case:

QUALIFY *;  
UNQUALIFY MonthYear;  
  
Table1:  
LOAD Category,  
    Month(Mês) as Month,  
    Year(Ano) as Year,
MonthName(DateField) as MonthYear,  
    Consume,  
    [Avg/day],  
    [Payed Value]  
FROM  abc;  
  
Table2:  
LOAD Month(Mês) as Month,  
    Year(Ano) as Year, 
MonthName(DateField) as MonthYear

    [Recuperation Grill],  
    [Recuperation paper],  
    [Recuperation coffee],  
    [Total Recuperation]  
FROM abc;  
 
UNQUALIFY *; 

[swuehl]

Just as a side note, the arguments of functions Year() and Month() should be date values, not year or month numbers.

Not sure how your Mes values look like, but I do assume that year values are like 2016, right?

So I believe you shouldn't apply Year function, but just rename your Ano field to Year:

...

Ano as Year,

...

Besides that, it might be better to create and use a YearMonth field (Maybe using Makedate(Ano, Mes) as YearMonth) in both tables and link to a common master calendar.

[#8]

This is how my database look like to Month and year:

[swuehl]

Does this work with your data?

Makedate( Ano, Month(Date#(Mes, 'MMMM'))) as YearMonth

[your long month names should be set in your script or OS to make it work]