how to write if condition making a variable?

[#1]

Hi everyone,

I've faced such a problem - in one table I have [debit account] and [credit account], and in another table - [client's account], which may match both credit and debit ones. to match the tables I want to create in table 1 a field [client's account], which should be counted so: if(left([debit account] = "40817", [debit account], [credit account]) (we know that in table 2 all accounts start with 40817 and the fields are text ones). should I do it while load procedure and how would it look like? Sorry for such a silly question, but I'm new to qlik and still have much to learn:-)

[MK_QSL]

Can you load some lines as a sample?

[ali_hijazi]

please give me sample records

[#4]

share some sample data

[puttemans]

Hi Anna,

If I'm correct, you want to create a 'connector' between both tables. The suggestion you make is valid, but then you do not make a distinction between your clients (you use 1 single number). Can you, by extending the number identify unique clients, and can I assume that each client only has either 1 debit or credit account in table 1?

[#6]

I'm sorry I'm not able to load xls here (work permissions), but it looks like that^

table 1

debit acc

40817710665000001877

62575810290270010759

40817810799620009740

credit acc (same rows)

50317710665000001866

40817710665000001877

91015810799620009740

table 2

client's acc

40817710665000001877

40817710665000001875

naturally, there are about 2500000 lines in table 1 and about 25000 in table 2

[#7]

You see, in table 1 I don't see the number of a client. each client hat only 1 client's number in table2, but may appear in several rows of table 1, both in debit and credit accounts (actually, it means, either he gets or spends money).

Actually, my problem can be described in another way - can we define a variable during load procedure, usinf if-statement? (i get a mistake here, although such deginitions as right(...<5) as [] work in another circumstances).

[puttemans · Accepted solution]

Hi Anna,

You can use the IF statement to create a variable while loading. Script looks like :

IF( varA>varB, X,Y) as Z,

If the condition is true, then X will be returned, else Y, and it will be written under variable Z.

[puttemans]

Try this please

100:

LOAD  [Дата и время создания],

     [Дата и время обработки],

     [Статус транзакции],

     [Статус папки],

     [Статус документа],

     [Счет дебет],

     [Сумма Дебет],

     [Счет Кредит],

     [Сумма Кредит],

     PAN,

     Терминал,

     Тип,

     [Код транзакции],

     [Сумма транзакции],

     [Валюта транзакции],

     Издатель,

     Эквайер,

     [Дата и время совершения транзакции],

     [Дополнительное описание транзакции],

     [Описание транзакции]

     FROM

(qvd);

noconcatenate

200:

LOAD [Дата и время создания],

     [Дата и время обработки],

     [Статус транзакции],

     [Статус папки],

     [Статус документа],

     [Счет дебет],

     [Сумма Дебет],

     [Счет Кредит],

     if(left [Счет дебет]="40817",[Счет дебет],[Счет Кредит]) as[Счет Клиента]

     [Сумма Кредит],

     PAN,

     Терминал,

     Тип,

     [Код транзакции],

     [Сумма транзакции],

     [Валюта транзакции],

     Издатель,

     Эквайер,

     [Дата и время совершения транзакции],

     [Дополнительное описание транзакции],

     [Описание транзакции]

RESIDENT 100;

DROP TABLE 100;

[Anonymous]

Anna

You were pretty much there in your question.  I'd tidy it up a bit and use this in the load script load statement :

     if ( left ( [YourAccountField] , 5  = '40817' , 'Debit Account' , 'Credit Account' )     as     [Account Type] ,