New variable

[#1]

Dear

i like to add a new variable "AnytimeCustomer" , it should work as

if customer as received at least one order with AlsisScope=J or  AlsisScope= G then anytimecustomer= yes, otherwise anytimecostemer=no

many thanks

[oknotsen]

Does it have to be a variable?

Sounds to me like you want to add a flag to your data model.

Assuming your customer table is called Customers and customers have a CustomerID, I suggest something like this:

Left Join(Customers)

Load

CustomerID,

if (AlsisScope = 'J', 'Yes', 'No') as anytimecustomer

Resident Customers

;

[#3]

Hi

thanks for your reply...not sure we understand each other.

I arrange a data sample

Frigo NW is an AnytimeCustomer (Yes) therefore he had at least one AlsisScope=J

Frigomeccanica SPA is not AnytimeCustomer (No) therefore never had at least one AlsisScope=J

Date	OrderNumber	CustomerName	Alsis	AlsisScope	AnytimeCustomer
12/01/2015 00:00	381466	Frigro NV	1372	J	Yes
12/01/2015 00:00	381466	Frigro NV	1372	G	Yes
13/01/2015 00:00	107676	Frigro NV	1372		Yes
13/01/2015 00:00	107676	Frigro NV	1372	J	Yes
13/01/2015 00:00	107676	Frigro NV	1372		Yes
13/01/2015 00:00	107676	Frigro NV	1372		Yes
13/01/2015 00:00	381482	Frigus UG (haftungsbeschrankt)	4473	G	Yes
13/01/2015 00:00	381483	Frigro NV	1372	J	Yes
13/01/2015 00:00	381483	Frigro NV	1372	J	Yes
13/01/2015 00:00	381496	FRIGOMECCANICA S.P.A.	1372	A	No
13/01/2015 00:00	381504	FRIGO TECNICA INTERNAZIONALE	4473	B	No
13/01/2015 00:00	381504	FRIGO TECNICA INTERNAZIONALE	4473	B	No
13/01/2015 00:00	381506	FRIGICOM SPOL s r.o.	4470	B	No
14/01/2015 00:00	107681	Frigro NV	4473		Yes
14/01/2015 00:00	107681	Frigro NV	4473	A	Yes
14/01/2015 00:00	107681	Frigro NV	4473	B	Yes
14/01/2015 00:00	381508	Frigoveneta Service Srl	4473	B	No
14/01/2015 00:00	381517	FRIGOMECCANICA S.P.A.	4473	B	No
14/01/2015 00:00	381521	Frigel AG	1372	A	No
16/01/2015 00:00	107704	Frigro NV	4473	G	Yes
16/01/2015 00:00	381538	Frigro NV	1372	J	Yes
16/01/2015 00:00	381545	Frigoterm Impianti S.r.l.	1372	A	No
16/01/2015 00:00	381547	Frigro NV	4473	G	Yes
16/01/2015 00:00	381547	Frigro NV	4473	G	Yes
16/01/2015 00:00	381547	Frigro NV	4473	G	Yes
17/01/2015 00:00	381558	FRIGRO NV	1372	A	Yes

[oknotsen]

Did you try the script I suggested?

How did that work for you?

[Agis-Kalogiannis]

You don't need a variable for that. You just need a new expression in you straight table

So, create a new expression, name it AnytimeCustomer, and for definition, type the following:

if(AlsisScope = 'J' OR AlsisScope = 'G', 'Yes', 'No')

Please let me know if this is what you want

Regards

Agis

[#6]

Hi,

if you look to the table the new variable AnytimeCust ,sometime has yes even if AlsisScope is A or B, because the costomer in past had an order with AlsisScope J or G

I believe that your function put yes only when find J or G in the row, and not if at least once in the history costomer had a J or G order.

[#7]

Thank for you feedback, I tried but sorry i have not idea how to insert the expression in the script. I have not a customer table, Customer name is a field in 2 different table I have joined

As you can see from script below

CONNECT TO Groningen (XUserId is eXQHBJVMQbbEHadJ);
Temp:
SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Descrizione,artikelvariant as ItemID,extordtyp,ordantal as Quantity,orddatum as Date,year(orddatum) as Year,month(orddatum) as Month,day(orddatum) as Day,saljare as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr

FROM JvsNL01.dbo.orp where orddatum>{ts '2013-01-01 00:00:00'} and varugruppkod<>'B'  order by OrderNumber;
Inner Join
SQL SELECT kundbestnr as CustomerOrderNumber,kundref2 as OrderHandler,ordstat as OrderStatus,slutkundnamn1 as CustomerName,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr FROM JvsNL01.dbo.oh where ordstat<>'90'order by OrderNumber;
Inner Join
SQL SELECT ftgnamn,ftgnr as AdministrativeEntity FROM JvsNL01.dbo.fr;

Groningen:
LOAD *,week(Date) as Week;
Load *, if(OrderNumber = peek(OrderNumber),0,1) as CountOrder;
Load *, if(match(AlsisScope, 'J') or match(AlsisScope, 'G') , 'Yes', 'No') as PotentialIECom;
Load *, TransfertPrice*Quantity as TransferPriceOrderQTY;
Load *, CustomerName, if (AlsisScope = 'J' or AlsisScope, 'Yes', 'No') as AnyTimeCustomer;
Load *, 'Groningen' as Supplyer 
RESIDENT Temp;
Drop Table Temp;

[Anonymous]

‌Hi

I might be missing something here but why are you reloading the Groningen table 6 times? I also notice that you are not using preceding load only SQL syntax. What Onno I suggesting is that you put his script after the Groningen table, as you don't have a customers table this would have to be replaced with Groningen ( I believe Onno was just assuming that the table was called Customers).

However I'm not sure this will give you what you want, this will I think give you the same result as Agis suggestion, a Yes or No based on the row data.

You might achieve this by using 1or 0 instead of Yes, No and calling the field counter and then using something like

if( sum( {1} counter) >0, 'Yes','No'). I'm on a mobile so I haven't really validated this yet. You could also look into SET analysis and use the (P) function in your table (check the help for correct syntax), this makes it possible to identify any customers that have at some time received an order with scope J or G.

I Also notice that you are missing the second scope (G) in your if statement in the script after the "Or" statement.

[vvvvvvizard]

CTR+ALT+V  click on add a new variable  Add name as AnytimeCustomer

In the definition place insert  the sytax = if(AlsisScope = 'J' OR AlsisScope = 'G', 'Yes', 'No')

[#10]

Hi

I followed your suggestion about load, then try to place the script but I am not able to find the right place for script "Load CustomerName, if (AlsisScope = 'J', 'Yes', 'No') as counter, if( sum(counter='1')>0, 'Yes','No') as anytimecustomer"

but it not work, please to see below

ODBC CONNECT TO Groningen (XUserId is eXQHBJVMQbbEHadJ);

Temp:

SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Descrizione,artikelvariant as ItemID,extordtyp,ordantal as Quantity,orddatum as Date,year(orddatum) as Year,month(orddatum) as Month,day(orddatum) as Day,saljare as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr

FROM JvsNL01.dbo.orp where orddatum>{ts '2013-01-01 00:00:00'} order by OrderNumber;

Inner Join

SQL SELECT kundbestnr as CustomerOrderNumber,kundref2 as OrderHandler,ordstat as OrderStatus,slutkundnamn1 as CustomerName,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr

FROM JvsNL01.dbo.oh where ordstat<>'90'order by OrderNumber;

Inner Join

SQL SELECT ftgnamn,ftgnr as AdministrativeEntity

FROM JvsNL01.dbo.fr;

Groningen:

LOAD *,week(Date) as Week, if(OrderNumber = peek(OrderNumber),0,1) as CountOrder,if(match(AlsisScope, 'J') or match(AlsisScope, 'G') , 'Yes', 'No') as PotentialIECom,TransfertPrice*Quantity as TransferPriceOrderQTY,'Groningen' as Supplyer;

Load CustomerName, if (AlsisScope = 'J', 'Yes', 'No') as counter, if( sum(counter='1')>0, 'Yes','No') as anytimecustomer

RESIDENT Temp;

Drop Table Temp;