topic Iteration for Selected Rows Only in QlikView

Iteration for Selected Rows Only

peter_burgess — Tue, 06 Jul 2021 02:18:31 GMT

Hi all.

I have a situation where I am loading in records where each row has an individual material name, and a weight for that material, plus some other fields such as date and time.

But a selected number of these materials are in fact made up of components, where the weight of the component is a known percentage of the original material. There are four components that make up each selected material, for example Component 1 is 0.5*the weight of the material, Components 2 and 3 are 0.2*the weight of the material, and Component 4 is 0.1*the weight of the material.

I want to be able to iterate these particular records so that whenever the selected material is encountered, that record is loaded 4 times, with each iteration causing the weight to be down-rated by the specified amount, and the Component registered as the material name.

But for all other materials, I just want the record loaded once.

I thought I might be able to use iterno() and a while statement, but I can't see how to reset iterno() dependent on material name.

Does anyone know of a technique I might use to achieve what I want?

Many thanks,

Pete

Re: Iteration for Selected Rows Only

avinashelite — Tue, 06 Jul 2021 03:32:31 GMT

you need to use loops to do this , could you please share the sample data to work on

Re: Iteration for Selected Rows Only

peter_burgess — Tue, 06 Jul 2021 04:05:33 GMT

Attached is a very simplified working example .

Table A is the table to be read / loaded by the script.

The component breakdown of the two PVC compounds is on the right, in percentage units.

The output required, i.e. the table that will exist in the QV document after loading, is given as Table B.

Hope this is understandable.

Pete

Re: Iteration for Selected Rows Only

avinashelite — Tue, 06 Jul 2021 07:32:45 GMT

Please find the solution below

Table_A:
LOAD * Inline
[
Material,Date,Weight
PVC-One,08/08/2021,200
PVC-Two,15/08/2021,200
HDPE,22/08/2021,80
LLDPE,29/08/2021,76
];

Left Join(Table_A)

Component_PVC:
LOAD * Inline
[
Material,Component,Percent
PVC-One,Resin,40
PVC-One,Plasticiser,30
PVC-One,Filler,20
PVC-One,Stabiliser,10
PVC-Two,Resin,30
PVC-Two,Plasticiser,30
PVC-Two,Filler,30
PVC-Two,Stabiliser,10
];

Final:
LOAD
Date,
If(len(trim(Component))=0,Material,Component) as Material,
Material as Material_Orginal,
If(len(trim(Component))=0,Weight,(Weight*Percent)/100) as Weight,
Weight as Weight_Orginal
Resident
Table_A;

DROP Table Table_A;

Re: Iteration for Selected Rows Only

peter_burgess — Tue, 06 Jul 2021 08:57:26 GMT

Thank you avinashelite, the correct approach is now clear to me. I had never even considered joining the component tables, although it seems obvious now. I was fixated on a one shot approach that would have been vey difficult to understand, with many if statements. I appreciate your putting me on the right path. 🙂

Best regards,

Pete

Re: Iteration for Selected Rows Only

avinashelite — Tue, 06 Jul 2021 09:51:29 GMT

Thank you !!