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: 
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
Master II
Master II

Yes!