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: 
peter_burgess
Contributor III
Contributor III

Iteration for Selected Rows Only

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

Labels (2)
1 Solution

Accepted Solutions
avinashelite

Please find the solution below

avinashelite_0-1625556711010.png

 

 


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;

View solution in original post

5 Replies
avinashelite

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

peter_burgess
Contributor III
Contributor III
Author

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

 

avinashelite

Please find the solution below

avinashelite_0-1625556711010.png

 

 


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;

peter_burgess
Contributor III
Contributor III
Author

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

avinashelite

Thank you !!