Hi all,
i'm currently working on a project where I need to convert existing DTS SQL scripts into QlikView. While getting the data over is no problem there are lots of data transformations being done in the existing SQL script that I need to replicate in QV.
For example, one DTS package does the following:
1) Extracts Sales Data into new table 'Sales Lines'
2) Updates 'Sales Lines' to set field a = 'Invoice', where Line Type = x
3) Updates 'Sales Lines' to set field a = 'Credit' where Line Type = y
4) Updates 'Sales Lines' to set field b = 'y' where a='Invoice' and Date <=d
5) Updates 'Sales Lines' to set field b = 'z' where a='Credit' and Date <=d
6).......many more transformations......
Ok, so as far as I am aware, QV does not have an equivalent UPDATE style statement (there is an update keyword, but I think it's to do with dynamic updates). I can do all of the following, but it would involve reading all lines once and then doing multiple RESIDENT load statements with WHERE clauses and a single transformation each time and then contatenating afterwards, before doing the next one.
My only other thought for a solution is to turn the DTS script into a Stored Procedure and leave it to do all of the work externally and I can then switch the Connection and use the results of the Stored Procedure.
Ideally, though I would like to do all of this inside QV. Does anyone have any other solutions that might be useful for me? Or any other advice on how best to reproduce these update statments inside QV?
Thanks in advance for any help.
