topic Re: Loop in Script Help in QlikView

Loop in Script Help

lockematthewp — Fri, 29 Mar 2019 14:08:25 GMT

I currently have a table with a few fields including TotalDataPoints and BadDataPoint. For example, I might have TotalDataPoints as 10 and BadDataPoint values of 2,5, and 9. I would like to make a table with all values from 1 to TotalDataPoints (20 in this example) so I can make another two fields called DataPoint and ValidPoint. This would have 'Good' unless it is equal to BadDataPoint, in which case it would be equal to 'Bad'.

Before:

TotalDataPoints	BadDataPoint
10	2
10	5
10	9

After:

TotalDataPoints	DataPoint	ValidPoint
10	1	Good
10	2	Bad
10	3	Good
10	4	Good
10	5	Bad
10	6	Good
10	7	Good
10	8	Good
10	9	Bad
10	10	Good

Re: Loop in Script Help

sunny_talwar — Fri, 29 Mar 2019 14:24:51 GMT

May be this

Table:
LOAD TotalDataPoints,
	 BadDataPoint as DatPoint,
	 'Bad' as ValidPoint;
LOAD * INLINE [
    TotalDataPoints, BadDataPoint
    10, 2
    10, 5
    10, 9
];

Concatenate (Table)
LOAD *
Where Not Exists(DatPoint);
LOAD Distinct TotalDataPoints,
	 IterNo() as DatPoint,
	 'Good' as ValidPoint
Resident Table
While IterNo() <= TotalDataPoints;

Re: Loop in Script Help

felcar2013 — Fri, 29 Mar 2019 14:33:02 GMT

try this

FOR vDataPoint= 1 to 10

tab:
Load *,
if(match(DataPoint,2,5,9),'Bad','Good') as ValidPoint
;
Load 10 as TotalDataPoint,
$(vDataPoint) as DataPoint
AutoGenerate 1
;