topic Re: how to write if condition making a variable? in QlikView

how to write if condition making a variable?

Wed, 05 Nov 2014 12:43:39 GMT

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:-)

Re: how to write if condition making a variable?

MK_QSL — Wed, 05 Nov 2014 12:53:07 GMT

Can you load some lines as a sample?

Re: how to write if condition making a variable?

ali_hijazi — Wed, 05 Nov 2014 12:55:06 GMT

please give me sample records

Re: how to write if condition making a variable?

Wed, 05 Nov 2014 12:56:48 GMT

share some sample data

Re: how to write if condition making a variable?

puttemans — Wed, 05 Nov 2014 13:02:58 GMT

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?

Re: how to write if condition making a variable?

Wed, 05 Nov 2014 13:31:46 GMT

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

Re: how to write if condition making a variable?

Wed, 05 Nov 2014 13:36:25 GMT

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).

Re: how to write if condition making a variable?

puttemans — Wed, 05 Nov 2014 13:48:57 GMT

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.

Re: how to write if condition making a variable?

puttemans — Wed, 05 Nov 2014 14:20:07 GMT

Try this please

100:

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

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

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

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

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

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

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

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

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

     PAN,

     Терминал,

     Тип,

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

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

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

     Издатель,

     Эквайер,

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

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

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

     FROM

(qvd);

noconcatenate

200:

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

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

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

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

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

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

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

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

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

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

     PAN,

     Терминал,

     Тип,

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

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

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

     Издатель,

     Эквайер,

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

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

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

RESIDENT 100;

DROP TABLE 100;

Re: how to write if condition making a variable?

Anonymous — Wed, 05 Nov 2014 14:23:19 GMT

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] ,

Re: how to write if condition making a variable?

puttemans — Wed, 05 Nov 2014 14:23:45 GMT

won't work as your left condition is not correct...