Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Find an efficient script getting 5 last Purchase Prices.

Sub Bedrag(ikp,aant,l,b,Hl,Hb,Ehkode) SWITCH Ehkode                 Case 'PLAAT'                     If Not('$(ikp)'=0) And Not('$(aant)'=0) And Not('$(l)'=0) And Not('$(b)'=0) And Not('$(Hl)'=0) And Not('$(Hb)'=0) Then                                 IF LTSTNIKP1 = 0 THEN                   LET LTSTNIKP1= ikp/(aant*(l/Hl*b/Hb));                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= ikp/(aant*(l/Hl*b/Hb));                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= ikp/(aant*(l/Hl*b/Hb));                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= ikp/(aant*(l/Hl*b/Hb));                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= ikp/(aant*(l/Hl*b/Hb));                 ENDIF                                 end if                 Case 'ZFPLAT'                 If Not('$(ikp)'=0) And Not('$(aant)'=0) And Not('$(l)'=0) And Not('$(b)'=0) And Not('$(Hl)'=0) And Not('$(Hb)'=0) Then                                 IF LTSTNIKP1 = 0 THEN                   LET LTSTNIKP1= ikp/(aant*(l/Hl*b/Hb));                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= ikp/(aant*(l/Hl*b/Hb));                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= ikp/(aant*(l/Hl*b/Hb));                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= ikp/(aant*(l/Hl*b/Hb));                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= ikp/(aant*(l/Hl*b/Hb));                 ENDIF                                 end if                                     Case 'LITER'                     If Not('$(ikp)' = 0) And Not('$(aant)' = 0) Then                         IF LTSTNIKP1 = 0 THEN                     LET LTSTNIKP1= ikp/aant;                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= ikp/aant;                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= ikp/aant;                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= ikp/aant;                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= ikp/aant;                 ENDIF                                         End If                                     Case 'KILO'                     If Not('$(ikp)' = 0) And Not('$(aant)' = 0) Then                     IF LTSTNIKP1 = 0 THEN                     LET LTSTNIKP1= ikp/aant;                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= ikp/aant;                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= ikp/aant;                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= ikp/aant;                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= ikp/aant;                 ENDIF                        End If                                     Case 'METER'                 If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then                     IF LTSTNIKP1 = 0 THEN                     LET LTSTNIKP1= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= ikp/(aant*(l/Hl));                 ENDIF                                         End If                 Case 'BUIS'             If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then                     IF LTSTNIKP1 = 0 THEN                     LET LTSTNIKP1= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= ikp/(aant*(l/Hl));                 ENDIF                                         End If                 Case 'ZFBUIS'                     If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then                     IF LTSTNIKP1 = 0 THEN                     LET LTSTNIKP1= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= ikp/(aant*(l/Hl));                 ENDIF                                         End If                                     Case 'STAF'                     If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then                         IF LTSTNIKP1 = 0 THEN                     LET LTSTNIKP1= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= ikp/(aant*(l/Hl));                 ENDIF                                 End If                 Case 'ZFSTAF'                     If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then                         IF LTSTNIKP1 = 0 THEN                     LET LTSTNIKP1= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= ikp/(aant*(l/Hl));                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= ikp/(aant*(l/Hl));                 ENDIF                                         End If                 DEFAULT                     If Not ($(ikp) = 0) And Not ($(aant) = 0) Then                     IF LTSTNIKP1 = 0 THEN                     LET LTSTNIKP1= $(ikp) / $(aant);                 ELSEIF LTSTNIKP2 = 0 THEN                 LET LTSTNIKP2= $(ikp) / $(aant);                 ELSEIF LTSTNIKP3 = 0 THEN                 LET LTSTNIKP3= $(ikp) / $(aant);                 ELSEIF LTSTNIKP4 = 0 THEN                 LET LTSTNIKP4= $(ikp) / $(aant);                 ELSEIF LTSTNIKP5 = 0 THEN                 LET LTSTNIKP5= $(ikp) / $(aant);                 ENDIF                                                             End If             ENDSWITCH End Sub; Sub Inkoop(Art,Hl,Hb,Ehkode) Let LTSTNIKP1=0; Let LTSTNIKP2=0; Let LTSTNIKP3=0; Let LTSTNIKP4=0; Let LTSTNIKP5=0; Let LTSTFDAT = Makedate(1900,1,1); ZoekInkoop: LOAD *, 1 AS TEST RESIDENT Inkreg where INKART='$(Art)' ORDER BY FAKTUURDATUM DESC; LET NumRowsZoekInkoop=NoOfRows('ZoekInkoop'); for i=0 to $(NumRowsZoekInkoop)-1 if NoOfRows(Total)>0 then LET artikel = peek('ZKINKART',$(i),'ZoekInkoop'); LET factdat = date#(peek('FAKTUURDATUM',$(i),'ZoekInkoop'),1);       If '$(factdat)' <> '$(LTSTFDAT)' Then                 LET LTSTFDAT = DATE('$(factdat)','YYYY-MM-DD');           CALL Bedrag(peek('INKNETTOIKP',$(i),'ZoekInkoop'),peek('INKONTV',$(i),'ZoekInkoop'),peek('INKLENGTE',$(i),'ZoekInkoop'),peek'INKBREEDTE',$(i),'ZoekInkoop'),'$(Hl)','$(Hb)','$(Ehkode)')               ENDIF  ENDIF       next i End Sub; LET NumRows=NoOfRows('VoorraadWaarde1'); for i=0 to $(NumRows)-1 Let Art = peek('ARTIKELNR',$(i),'VoorraadWaarde1'); Let Hl = peek('HANDELSLENGTE',$(i),'VoorraadWaarde1'); Let Hb = peek('HANDELSBREEDTE',$(i),'VoorraadWaarde1'); Let Ehkode = peek('EENHEIDSKODE',$(i),'VoorraadWaarde1'); CALL Inkoop('$(Art)','$(Hl)','$(Hb)','$(Ehkode)'); //CALL Inkoop('0901C400'); Voorraadwaarde2: LOAD *, ARTIKELNR AS VRDARTIKELNR, If('$(LTSTNIKP1)'>0,'$(LTSTNIKP1)',0) AS NIKP1, If('$(LTSTNIKP2)'>0,'$(LTSTNIKP2)',0) AS NIKP2,   If('$(LTSTNIKP3)'>0,'$(LTSTNIKP3)',0) AS NIKP3,   If('$(LTSTNIKP4)'>0,'$(LTSTNIKP4)',0) AS NIKP4,   If('$(LTSTNIKP5)'>0,'$(LTSTNIKP5)',0) AS NIKP5   RESIDENT VoorraadWaarde1; NEXT i; The above used tables are allready filled in a different sheet. I only show you the script where I work with the data. The above script loops through an table with Articels (Materials) ==> Voorraadwaarde1. With the 'CALL Inkoop' get a selection of Purchaserows (Inkreg) to retrieve the Purchase prices based on Article and Invoicedate DESC. Found data is stuffed in Zoekinkoop. Next is the CALL Bedrag which calculates a netto price. While running the script QV takes a long time (every second a record) to retrieve Purchase prices, Calculations and update in a new table Voorraadwaarde2. TO do this for 5000 articles is difficult. VB.NET is faster. Question is: can this procedure be done faster, more efficient?

1 Reply
danielrozental
Honored Contributor II

Re: Find an efficient script getting 5 last Purchase Prices.

Yes!