Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

multiview
Contributor II

Looping through records in Qlikview? (As in Oracle / SAP ABAP)

Hi All


I am working on a report that takes long time to processes, so thought of doing it in QlikView. How could I simulate the below logic (script) assuming I have both tables in DBMS:

//Creating a cursor to loop through the sales lines

cursor mytable as select * from customer_order where year = 2012;


string DocType := '';

//For each record check

foreach myrec in mytable

if myrec.document_type = '02' then

DocType := 'Sales';

else if myrec.document_type = '03' then

DocType := 'Return';

else

DocType := 'Sales';

end if


insert into mytable 2 values (myrec.document_no, DocType, ....)


end foreach


Thank you in Advance!

Regards
MultiView

Message was edited by: Multi View

1 Solution

Accepted Solutions
Not applicable

Looping through records in Qlikview? (As in Oracle / SAP ABAP)

Hi

you can do this in your script:

MyTable:

LOAD *;

SQL

SELECT *

FROM CUSTUMER_ORDER

WHERE YEAR = 2012;

MyTable2:

LOAD Document_No,

     IF(Document_type = '02', 'Sales',

       IF(Document_type = '03', 'Return', 'Sales')) AS Doc_type

RESIDENT MyTable;

6 Replies
Not applicable

Looping through records in Qlikview? (As in Oracle / SAP ABAP)

Hi

you can do this in your script:

MyTable:

LOAD *;

SQL

SELECT *

FROM CUSTUMER_ORDER

WHERE YEAR = 2012;

MyTable2:

LOAD Document_No,

     IF(Document_type = '02', 'Sales',

       IF(Document_type = '03', 'Return', 'Sales')) AS Doc_type

RESIDENT MyTable;

multiview
Contributor II

Looping through records in Qlikview? (As in Oracle / SAP ABAP)

Thanks Eduardo . I think I am limited to simple expression inside LOAD statement. Can I select a value from different table while looping through the Mytable table?


Regards
MultiView

Employee
Employee

Looping through records in Qlikview? (As in Oracle / SAP ABAP)

No, you are not limited to expressions inside the LOAD. You can for instance do a for-next loop like:

MyTable:

Load Myrec from ... ;

For vValueNo = 1 to NoOfRows('MyTable')

     Load

          <fieldlist where $(vValueNo) is used; e.g.  peek('MyRec', $(vValueNo)-1,'MyTable')>

     from <some other table> ;

next vValueNo

/HIC

multiview
Contributor II

Looping through records in Qlikview? (As in Oracle / SAP ABAP)

Thank you HIC

multiview
Contributor II

Looping through records in Qlikview? (As in Oracle / SAP ABAP)

Henric,

How can I do this :

how can I compare a value from peek statement? Does it return the value?

E.g the equivalent of below :

select document_type
into mydoc_type
from sales
where...;

if (mydoc_type = '02')
...
else
end if;


Regards
MultiView

Employee
Employee

Re: Looping through records in Qlikview? (As in Oracle / SAP ABAP)

The peek(<Field>,<RecNo>,<TableName>) function returns a value - the value of the field <Field> on record <RecNo> (minus one) in table <Table>. So you can use this for a comparison, yes. For example:

if( peek( ... ) = '02', 'Sales', 'Return') as NewField

But I am not sure I understand what you want to do. In your initial example, you use "mytable" as input, and then you end your code by inserting two fields in "mytable". To do this, you do not need a for-next loop. You can use Eduardo's example, or even more straightforward, a preceding LOAD:

MyTable:

LOAD Document_No,

     IF(Document_type = '02', 'Sales',

       IF(Document_type = '03', 'Return', 'Sales')) AS Doc_type;

SQL SELECT * FROM CUSTUMER_ORDER

WHERE YEAR = 2012;

The result from the SELECT is piped into the preceding LOAD, so this way the LOAD can be used to create new fields or to filter the data further.

/HIC

Community Browser