Operator IF

[#1]

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

[marcus_sommer]

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

[#3]

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

[marcus_sommer]

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

[#5]

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

[marcus_sommer · Accepted solution]

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

[#7]

Thank very kind!