Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
Please, I have 2 questions.
i would like to create a column from SQL DB's extracted that is a function of extracted SQL DB columns themself.
1) Column in db: orderstatus, ordernumber, product etc..
example:
Orderstatus , Product, Ordernumber, C ountOrders
xx.... xx.... xx..... xxx.....
70 A 2561 1
70 A 2562 1
70 B 2562 0
90 A 2567 0
90 C 2567 0
the ordernumber should be ordered ascending, and ordernumber can be repeated in dataset
I would like to count it once ordernumber.
i belived it will be something like this if(and(OrderStatus<>'90',OrderNumber(i)<>OrderNumber(i+1)),1,0) as CountOrders
where OrderNumber(i )<>OrderNumber(i+1)) it would mean if the row (i ) is not equal to row (i+1) then row(i) for CountOrders=1
2) Columns in db: CustomerName, OrdersScope,Ordersnumbers
example
CustomerName OrdersScope Ordernumber PotentialE-Com
Pluto J 2561 Yes
Pluto A 2562 Yes
Quo J 2562 Yes
Qua A 2567 No
I would create a column as (PotentialE-Com) where if customer at least once has OrdersScope equal to J or G
then PotentialE-Com=Yes
Please could you help me on that? thanks
Try:
First:
Load *, if(Ordernummer = peek('Ordernummer'), 0, 1) as OrderCountFlag Order by Ordernummer;
SQL Select * From YourDatabase;
Second:
Load CustomerName, OrderScope, OrderNumber, if(match(OrderScope, 'J'), 'Yes', 'No') as [PotentialIE-Com];
SQL Select * From YourDatabase;
- Marcus
I wouldn't try to make such things within the SQL. I suggest to create such flags or counting anything with one or several preceeding loads and/or store the blank sql-request in a qvd and use them for further transforming, about in this way:
Load A, B, C, ... count(anything) as XYZ group by A, B, C;
Load *, if(Ordernummer = peek('Ordernummer'), 'Then This', 'Then That') as xyz Order by Ordernummer;
SQL Select ... From ...;
- Marcus
I don't understand why we group for product, moreover, i have thousands of products type (A,B,C, D......AB, ....) and every data reflesh I can have one new, how can "load" all them?
thanks
There wasn't a group on products meant - it's only an example of possible way to use preceeding loads as second and third step from origin load with a suggestions on how could be records could be compared (peek-function) and possibly counted or ...
- Marcus
ok good.
please about
2) Columns in db: CustomerName, OrdersScope,Ordersnumbers
example
CustomerName OrdersScope Ordernumber PotentialE-Com
Pluto J 2561 Yes
Pluto A 2562 Yes
Quo J 2562 Yes
Qua A 2567 No
I would create a column as (PotentialE-Com) where if customer at least once has OrdersScope equal to J or G
then PotentialE-Com=Yes
do you have some feedback to give me?
thanks
Try:
First:
Load *, if(Ordernummer = peek('Ordernummer'), 0, 1) as OrderCountFlag Order by Ordernummer;
SQL Select * From YourDatabase;
Second:
Load CustomerName, OrderScope, OrderNumber, if(match(OrderScope, 'J'), 'Yes', 'No') as [PotentialIE-Com];
SQL Select * From YourDatabase;
- Marcus
Thank very kind!