topic LOOP in QlikView

LOOP

Anonymous — Fri, 10 Dec 2010 11:58:30 GMT

Because this script just a record breaking quantities of parts?

tmp:

load * inline[

DATA, PARCELAS

'11/10/2011',20

'21/10/2011',7

];

LET X = peek('PARCELAS');

LET Z = peek('DATA');

FOR i = 1 to $(x)

load * inline

[

DATA, PARCELAS

$(z),$(i)

];

next i

LOOP

Anonymous — Fri, 10 Dec 2010 15:18:39 GMT

Can anyone help?

LOOP

marcel_olmo — Fri, 10 Dec 2010 16:48:44 GMT

What do you mean with break?

Which is your desired result? Maybe if you are more specific, we can help you easily. 😉

This is what you get right now. Right?

DATA	PARCELAS
	1
	2
	3
	4
	5
	6
	7
40827	20
40837	7

LOOP

Anonymous — Fri, 10 Dec 2010 17:05:00 GMT

Exactly.
This is what I have now.
You can observe that the value did not break 20.

My table is comprised of several records with different field plots.

In my example, I posted only two record, just one more record breaking obeyed.

LOOP

marcel_olmo — Fri, 10 Dec 2010 17:39:04 GMT

Sorry Washington, I still don't get you when you say "the value did not break 20" ¿?

Could you put an example of your desired result, like I showed you my final table result before?

Thank you.

LOOP

Fri, 10 Dec 2010 18:02:03 GMT

did you mean this?

tmp:
load * inline [
DATA, PARCELAS
'11/10/2011',20
'21/10/2011',7
];

Counts:
LOAD Count(PARCELAS) as Y
Resident tmp;

LET y = Peek('Y', 0) - 1;
DROP Table Counts;

for k = 0 to $(y)

 LET x = peek('PARCELAS', $(k));
 LET z = peek('DATA', $(k));

 FOR i = 1 to $(x)

 load * inline [
 DATA, PARCELAS
 $(z),$(i)
 ];

 next i

NEXT k

But be aware about duplication of two first records.

LOOP

Anonymous — Fri, 10 Dec 2010 19:04:43 GMT

Beauty, solved my problem.

Thanks.

LOOP

Fri, 10 Dec 2010 19:11:58 GMT

Hi,

I change the script for not duplicate.


Datas:
load * inline [
DATA, PARCELAS
'11/10/2011',20
'21/10/2011',7
];


FOR k = 0 TO (NoOfRows('Datas')-1)

 LET data = peek('DATA', $(k));
 LET parcela = peek('PARCELAS', $(k));

 FOR i = 1 to ($(parcela)-1)

 load
 Date('$(data)') as DATA,
 '$(i)' as PARCELAS
 Autogenerate(1);

 NEXT i

NEXT k

LOOP

Anonymous — Mon, 13 Dec 2010 12:06:36 GMT

Fernando,

Because in your example the second record did not break like the original?

LOOP

Mon, 13 Dec 2010 14:05:46 GMT

Washington,

I did a test here and it worked properly.

Following is the table with the data.

DATA	PARCELAS	count(DATA&PARCELAS)
		27
11/10/2011	1	1
11/10/2011	2	1
11/10/2011	3	1
11/10/2011	4	1
11/10/2011	5	1
11/10/2011	6	1
11/10/2011	7	1
11/10/2011	8	1
11/10/2011	9	1
11/10/2011	10	1
11/10/2011	11	1
11/10/2011	12	1
11/10/2011	13	1
11/10/2011	14	1
11/10/2011	15	1
11/10/2011	16	1
11/10/2011	17	1
11/10/2011	18	1
11/10/2011	19	1
11/10/2011	20	1
21/10/2011	1	1
21/10/2011	2	1
21/10/2011	3	1
21/10/2011	4	1
21/10/2011	5	1
21/10/2011	6	1
21/10/2011	7	1

LOOP

Anonymous — Mon, 13 Dec 2010 14:28:06 GMT

Fernando,

I'm using the example that you have not left over is breaking the second record:

Datas:
load * inline [
DATA, PARCELAS
'11/10/2011',20
'21/10/2011',7
];

FOR k = 0 TO (NoOfRows('Datas')-1)

LET data = peek('DATA', $(k));
LET parcela = peek('PARCELAS', $(k));

FOR i = 1 to ($(parcela)-1)

load
Date('$(data)') as DATA,
'$(i)' as PARCELAS
Autogenerate(1);

NEXT i

NEXT k

LOOP

Mon, 13 Dec 2010 17:00:55 GMT

OK,

I understand the problem.

change the command autogenerante.



load
Date('$(data)','DD/MM/YYYY') as DATA,
'$(i)' as PARCELAS
Autogenerate(1);

OR
change the system variables to Portuguese



SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';
SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

LOOP

Anonymous — Mon, 13 Dec 2010 17:56:04 GMT

Fernando,

Solved the problem.

Now I'm using this script to a QVD.

Only one field that is not working. It is the field 'Valor_Compra'.

If you can help spin grateful again.

Thanks.

Datas:

LOAD

COD_FORNECEDOR

Valor_Compra

Parcelas

Prazos

FROM

C:\Importa\tabela.qvd(

qvd);

FOR

LET

FOR

load

Date

('$(data)','DD/MM/YYYY')+('$(Prazo)'*'$(i)') as Data,

'$(i)'

as Parcelas,

'$(fornecedor)'

as COD_FORNECEDOR,

'$(V_Compra)'

as Valor_Compra,

'$(Prazo)'

as Prazos

Autogenerate

(1);

k i = 1 to ($(parcela)-1) Prazo = peek('Prazos', $(k)); V_Compra = peek('Valor_Compra', $(k)); fornecedor = peek('COD_FORNECEDOR', $(k)); parcela = peek('Parcelas', $(k)); data = peek('Data', $(k)); k = 0 TO (NoOfRows('Datas')-1) date(Data,'DD/MM/YYYY') AS Data,

LOOP

Tue, 14 Dec 2010 11:57:11 GMT

Washington,

I can't understand the code you put in.

post the QVW fil.

LOOP

Anonymous — Tue, 14 Dec 2010 12:09:15 GMT

Fernando,

Note that the field 'Valor_compra' is for each record with two values.

Also, I need the field 'Plots' come with the original, without breaking. So I have two fields 'Plots', breaking and without breaking.

I think with these settings I finalize the application.

Thank you.

LOOP

Tue, 14 Dec 2010 12:53:51 GMT

You can try this code.


qualify *;
unqualify COD_FORNECEDOR;

Datas:
LOAD date(Data,'DD/MM/YYYY') AS Data,
 COD_FORNECEDOR,
 //Round( Valor_Compra) as Valor_Compra,
 Valor_Compra,
 Parcelas,
 Prazos

FROM tabela.qvd(qvd);

Unqualify *;


FOR k = 0 TO (NoOfRows('Datas')-1)

 LET data = peek('Datas.Data', $(k));
 LET parcela = peek('Datas.Parcelas', $(k));
 LET fornecedor = peek('COD_FORNECEDOR', $(k));
 LET V_Compra = peek('Datas.Valor_Compra', $(k));
 LET Prazo = peek('Datas.Prazos', $(k));
 LET vValorParcela = Num('$(V_Compra)')/Num('$(parcela)');

 FOR i = 0 to ($(parcela)-1)

 Parcelas:
 LOAD
 Date(Date('$(data)','DD/MM/YYYY')+('$(Prazo)'*'$(i)'),'DD/MM/YYYY') as Data_Vencimento,
 '$(i)'+1 as Parcelas,
 '$(parcela)' as Total_Parcelas,
 '$(fornecedor)' as COD_FORNECEDOR,
 '$(V_Compra)' as Valor_Compra,
 '$(Prazo)' as Prazos,
 '$(vValorParcela)' as Valor_Parcela
 AUTOGENERATE(1);

 NEXT i

 STORE Parcelas INTO TMP_Parcelas_$(fornecedor).qvd;
 DROP TABLE Parcelas;

NEXT k


Parcelas:
LOAD * FROM TMP_Parcelas_*.qvd (qvd);

LOOP

Anonymous — Tue, 14 Dec 2010 15:01:04 GMT

Fernando,

Now it worked.

Thank you.

LOOP

Mon, 31 Jan 2011 13:10:19 GMT

Hi,

You ca try this, in autogenerate.



Load
Date('$(data)','DD/MM/YYYY')+('$(Prazo)'*'$(i)') as Data,
'$(i)' as Parcelas,
'$(fornecedor)' as COD_FORNECEDOR,
Round('$(V_Compra)',0,001) as Valor_Compra,
'$(Prazo)' as Prazos
Autogenerate(1);