Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
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
Thank you HIC
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
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