Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Author

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;

View solution in original post

6 Replies
Not applicable
Author

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;

Anonymous
Not applicable
Author

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

hic
Former Employee
Former Employee

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

Anonymous
Not applicable
Author

Thank you HIC

Anonymous
Not applicable
Author

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

hic
Former Employee
Former Employee

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