topic Re: Expression Works But not in the script. in QlikView

Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 16:10:05 GMT

Hello, I have this expression: =Sum({<DebtDays={'123456'}>}TotalDebt) Works Fine:

But in the script I put:

Est:
Load Num_Card_OP,

If(Sum(DebtDays= 123456),TotalDebt) as Estratificacion

resident table1
Group By Num_Card_OP;

Thanks.

Re: Expression Works But not in the script.

MayilVahanan — Mon, 06 Aug 2012 16:41:49 GMT

Try like this,

Load Num_Card_OP,

If(DebtDays= '123456',Sum(TotalDebt)) as Estratificacion
resident table1

Groupby

TotalDebt , Num_Card_OP;

Hope it helps

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 16:51:15 GMT

Thanks for your reply: Works but drop many values with my expression I get just one total. Miss something?

Re: Expression Works But not in the script.

MayilVahanan — Mon, 06 Aug 2012 17:10:08 GMT

Its calculate the value for DebtDays= '123456'.. and take value(TotalDebt) for that.What is your requirement

Re: Expression Works But not in the script.

Mon, 06 Aug 2012 17:32:28 GMT

You can also flag records (in the script) that have DebtDays = '123456' and then use the flag in your set analysis. that way you'll have both DebtDays and non-DebtDays values.

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 17:43:34 GMT

The requirement is get phases for debt days (Debt Days = DateDebt - endMonth).So

If debt days = 123456 1-30 days,

If debt days =123456789 31-60 and so on. I left a QlikView file for better comprehenssion. Finally I have to build a table with Stratos by Total debt for sectors.

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 18:23:53 GMT

Any Help please

Thanks.

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 18:46:38 GMT

Could you please put an example?

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 19:01:47 GMT

I tried:

(DiasMora = 41060, Dual('Al Día',1),
If(DiasMora >= 1 And DiasMora <=30, Dual('1-30 Días',2))) as Estratificacion

But QlikView drop a Aggregation Problem.

Re: Expression Works But not in the script.

MayilVahanan — Mon, 06 Aug 2012 19:02:40 GMT

in this,

Estratificacion:

Load Num_Tarjeta_OP,

if(DiasMora = 41060, sum(Deuda_Total)) as Estratificacion

//If(DiasMora = 41060, Dual('Al Día',1)) as Estratificacion

/*if(sum(DiasMora)>=1 And sum(DiasMora)<=30, Dual('1-30 Días',2),

if(sum(DiasMora)>=31 And sum(DiasMora)<=60, Dual('31-60 Días',3),

if(sum(DiasMora)>=61 And sum(DiasMora)<=90,Dual('61-90 Días',4),

if(sum(DiasMora)>=91 And sum(DiasMora)<=120,Dual('91-120 Días',5),

if(sum(DiasMora)>=121 And sum(DiasMora)<=150, Dual('121-150 Días',6),

if(sum(DiasMora)>=151 And sum(DiasMora)<=180, Dual('151-180 Días',7),

if(sum(DiasMora)>=181 And sum(DiasMora)<=210,Dual('181-210 Días',8),

if(sum(DiasMora)>=211 And sum(DiasMora)<41029, Dual('>211 Días',9)))))))))) as Estratificacion*/

resident Cartera

Group By Deuda_Total,Num_Tarjeta_OP;

sum(DiasMora) value?

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 19:07:03 GMT

They are tries in the past. Now I tried to use just DiasMora. But QlikView Drops an aggregation problem.

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 19:25:20 GMT

Sure, The principal bases contains dates (DiasMora = Debtdate - EndMotnh) So If 20/07/2012 - 31/07/2012 = 11 Days.

If(DiasMora = 41060, Dual('Al Día',1), // 41060 its a wired date but means no debt

If(DiasMora >= 1 And DiasMora <=30, Dual('1-30 Días',2))) as Estratificacion // Here would go 20/07/2012-31/07/2012 = 11 Days.

In the final Table my dimmension it would be Estratificacion and my Expression Sum(Deuda_Total)

The final table Would be

estratificacion DeudaTotal

Al Dia 356000

1-30 875441

31-60 554545

and so on.

Re: Expression Works But not in the script.

MayilVahanan — Mon, 06 Aug 2012 19:38:00 GMT

You can use like this,

I think , this is one of the way to achieve your requirement..

Load if(DiasMora >= [minimumvalue of datefield]+1 and DiasMora <= [minimumvalue of datefield]+30, Dual('1-30 Días',2))

[minimumvalue of datefield] for example, in textbox give expression like this, Num(min(DateField))

Like wise you can get the value..

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 19:43:40 GMT

Im Sorry I didn't Understand the datafield value. Could You please put an example?

Thank you.

Re: Expression Works But not in the script.

MayilVahanan — Mon, 06 Aug 2012 19:48:09 GMT

DiasMora = 41060. is this dateField?

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 19:54:28 GMT

is endmonth by databases. for example 41060 = 31/05/2012 Because de databases is for all may. and so on.

Look the jpg example.

Re: Expression Works But not in the script.

MayilVahanan — Mon, 06 Aug 2012 20:01:13 GMT

Ok..Do you know the starting date of your database? You can use like this, MonthStart(Lowest date in your database)

for example 41060 = 31/05/2012

but starting date? MonthStart(date(41060) ) gives 01/05/2012

Like this, which is the starting date? Something like 01/01/2010

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 20:10:52 GMT

I dont have Starting date I have just PeriodoMora(This field) PeriodoMora-EndMonth (41060)= Nº DaysMora (DebtDays)

PeriodoMora-EndMonth (41060) = 41060 means the client desn't have debt. So he is at day.

If debtdays for example is 50 I have to put in > 31 and < 60 and so on. After that I have to build the table with all differents days debt and thouse stratifications for month. because the databases is in Visual Fox pro extension dbf Im creating may.

Re: Expression Works But not in the script.

MayilVahanan — Mon, 06 Aug 2012 20:38:18 GMT

Then you simply use,

if(DebtDays > 31 and DebtDays < 60, '31-60 days') like that

Re: Expression Works But not in the script.

pgalvezt — Mon, 06 Aug 2012 21:15:06 GMT

Doesn't Work

If(DiasMora = 41060, Dual('Al Día',1)) as Estratificacion

If(DiasMora>=31 And DiasMora <=60, Dual('31-60 Días',3),

if(DiasMora>=31 And DiasMora<=60, Dual('31-60 Días',3),

if(DiasMora>=61 And DiasMora<=90, Dual('61-90 Días',4),

if(DiasMora>=91 And DiasMora<=120, Dual('91-120 Días',5),

if(DiasMora>=121 And DiasMora<=150, Dual('121-150 Días',6),

if(DiasMora>=151 And DiasMora<=180, Dual('151-180 Días',7),

if(DiasMora>=181 And DiasMora<=210, Dual('181-210 Días',8),

if(DiasMora>=211 And DiasMora<41029, Dual('>211 Días',9))))))))))) as Estratificacion

Look Happen some rare When reload the QlikView Al dia = 1651913 (Toooo High)

But when I put in my Expression This:

=Sum({<DiasMora={'41060'}>}Deuda_Total)

QlikView bring me the real value 327347 (The same value that in Excel File). But I dont want the hiugh formula in my expression because QlikView its getting too slow.

Please look up the jpg file.