qualify not working with "concatenate"

[Anonymous]

I have the following script.   The first filed load with the prefix Products as per normal but the second load do not load with the Products prefix.  Is this not possible with Concatenate?

QUALIFY*;

Products:
LOAD
  Account

,AccountNo

,Product;

SQL
select

  accountid as Account
,accountNumber as AccountNo

,product as Product

  from Products;

Concatenate(Products)
LOAD
Account
,AccountNo

,ProductType;

SQL
select

  accountid as Account
,accountNumber as AccountNo

,case

      when ProductIdName like 'web' then 'Webber'
      when ProductIdName like 'work' then ''Barbeque'
           else ProductIdName
end as ProductType

from
ProductOS

UNQUALIFY *;
 

[ajsjoshua]

Hi,

Try this

QUALIFY*;

Products:
LOAD
  Account

,AccountNo

,Product;

SQL
select

  accountid as Account
,accountNumber as AccountNo

,product as Product

  from Products;

QUALIFY *;
UNQUALIFY [Account];
LOAD
Account
,AccountNo

,ProductType;

SQL
select

  accountid as Account
,accountNumber as AccountNo

,case

      when ProductIdName like 'web' then 'Webber'
      when ProductIdName like 'work' then ''Barbeque'
           else ProductIdName
end as ProductType

from
ProductOS;

[tamilarasu]

Hi Lauracastagna,

Try this,

SQL_Products:

LOAD
  Account

,AccountNo

,Product;

SQL
select

  accountid as Account
,accountNumber as AccountNo

,product as Product

  from Products;

Concatenate(SQL_Products)

LOAD

Account
,AccountNo

,ProductType;

SQL
select

  accountid as Account
,accountNumber as AccountNo

,case

      when ProductIdName like 'web' then 'Webber'
      when ProductIdName like 'work' then ''Barbeque'
          else ProductIdName
end as ProductType

from
ProductOS;

QUALIFY *;

Products:

LOAD * Resident SQL_Products;

DROP Table SQL_Products;

UNQUALIFY *;

[Anonymous]

Hi Tamil,

it does place all under the one table but it creates table = "SQL_Products-1 table"?

why does it create the '-1' ?

Cheers

[tamilarasu]

Hi Laura,

Did you mention the table name in resident load.?

QUALIFY *;

Products:

LOAD * Resident SQL_Products;

DROP Table SQL_Products;

[sunny_talwar]

Another option:

QUALIFY *;

Products:
LOAD Account,

          AccountNo,

          Product;

SQL Select

  accountid as Account,

  accountNumber as AccountNo,

  product as Product

FROM Products;

UNQUALIFY *;

Concatenate(Products)
LOAD Account as Products.Account,

          AccountNo as Products.AccountNo,

          ProductType as ProductProductType;

SQL Select

  accountid as Account,

  accountNumber as AccountNo,

  case

      when ProductIdName like 'web' then 'Webber'
      when ProductIdName like 'work' then ''Barbeque'
          else ProductIdName
end as ProductType

FROM ProductOS;