Do Loop

Tue, 31 Jan 2012 21:58:15 GMT

Trying to code a do loop in my script, but getting a script error. If the syntax is correct the table label (SalesDetails) will be in italics and the Load statement will be blue. In this example that is not the case. If I comment out the do statement, then syntax is correct. Can you help? Thanks.

Should be

Do when sub < 13

SalesDetails:

Load

'N/A' as Week,

LET sub = 1;

Do when sub < 13
SalesDetails:
Load
// Change At Beginning of Year Start
'N/A' as Week,
'N/A' as Day,
'N/A' as WeekDay,
'N/A' as [Week Ending Date],
If ($(sub) = 1, date('01/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 2, date('02/28/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 3, date('03/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 4, date('04/30/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 5, date('05/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 6, date('06/30/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 7, date('07/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 8, date('08/30/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 9, date('09/30/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 10, date('10/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 11, date('11/30/' & '$(TwoYearsAgo)','MM/DD/YY'),
If ($(sub) = 12, date('12/31/' & '$(TwoYearsAgo)','MM/DD/YY'))))))))))))) as [Invoice Date],
'$(TwoYearsAgo)' as Year,
If ($(sub) = 1, 'Jan',
If ($(sub) = 2, 'Feb',
If ($(sub) = 3, 'Mar',
If ($(sub) = 4, 'Apr',
If ($(sub) = 5, 'May',
If ($(sub) = 6, 'Jun',
If ($(sub) = 7, 'Jul',
If ($(sub) = 8, 'Aug',
If ($(sub) = 9, 'Sep',
If ($(sub) = 10, 'Oct',
If ($(sub) = 11, 'Nov',
If ($(sub) = 12, 'Dec', )))))))))))) as Month,
// Change At Begging of Year End
'Rolling' as TYPE,
null() as [Line Number],
'N/A' as YearKey,
'N/A' as YearKey2,
'N/A' as [Customer&Year],
'N/A' as [Line Type],
'N/A' as [Order Number],
'N/A' as [Invoice Number],
'N/A' as [Doc Type],
'N/A' as SDPA8,
'N/A' as SDMCU,
SDITM,
'N/A' as [Business Unit Detail],
text(SDAN8) as [Bill To Address],
'N/A' as [Ship To Address],
SDDGL as [Accounting Date],
'N/A' as [Ship Date],
'N/A' as [Promise Date],
// end of loading Dates
SDITM as [Short Name], // Link To Item Master
// SDLITM as [Item Number],
// SDDSC1 as [Decsription 1],
'N/A' as [Description 2],
'N/A' as [Order Type],
'N/A' as [U/M],
0 as [Open Order QTY ordered LB],
0 as [Open Order QTY Shipped LB],
0 as [Open Order QTY BackOrdered LB],
0 as [Open Order QTY ordered],
0 as [Open Order QTY Shipped],
0 as [Open Order QTY BackOrdered],
0 as [QTY ordered LB],
0 as [QTY Shipped LB],
0 as [QTY ordered],
0 as [QTY Shipped],
'N/A' as [Unit Weight],
'N/A' as [Weight U/M],
0 as [Sales Price],
0 as [SALES],
0 as [Sales Margin],
0 as [FREIGHT],
0 as [Open Order Amount],
0 as [Open Order Amount BackOrdered],
0 as [Open Order Margin],
0 as [Open Order FREIGHT],
0 as [Cost Price],
0 as [Extended Cost Amount],
0 as [Open Order Extended Cost Amount],
'N/A' as [Last Status],
'N/A' as SDDCTO,
'N/A' as SDRCD,
//num(SDSLSM) as [Sales Rep],
'N/A' as [Sales Rep],
SDDCTO&'_'&SDDOCO&'_'&SDLNID as SalesKey,
0 as [Replacement Cost],
0 as [Replacement Margin],
0 as [Open Order Replacement Cost],
0 as [Open Order Replacement Margin],
0 as [Forecast Qty],
0 as [Original Forecast],
0 as [Sales Budget],
0 as [Margin Budget],
0 as [Margin $ Budget],
0 as [QTY Budget],
'N/A' as [Freight Handling],
'N/A' as Carrier,
if(SDUOM = 'KG',(SDSOQS/10000)*2.2046,if(SDUOM = 'LB',SDUORG/10000,0)) as [R QTY Shipped LB],
if(SDLNTY <> 'F',SDAEXP/100,0) as [R SALES],
if(SDLNTY <> 'F',(SDAEXP/100) - (SDECST/100) ,0) as [R Sales Margin]
;

SQL SELECT
SDFY,SDLNTY,SDITM,SDDOCO,SDDOC,SDDCT,SDMCU ,SDFY,SDAN8 ,SDSHAN,SDIVD,SDDGL ,SDLITM,SDDSC1,SDDSC2,SDUOM ,SDSOBK,SDADDJ,
SDUORG,SDSOQS ,SDITWT,SDWTUM,SDUPRC,SDRSDJ,SDLTTR,SDDCTO,SDRCD,SDAEXP,SDLNID,SDUNCS,SDECST,SDDCTO,SDTRDJ,SDPA8,SDURAT, SDFRTH, SDCARS
FROM "JDE_PRODUCTION".PRODDTA.F42119
where SDIVD > 108031 and SDIVD < 109032
and SDLNTY IN ('A','C','D','E','F','G','H','S','M','N','Q','K','R','Y','YR','Z','FP','SC')
and SDDCTO IN ('SO','SV','CO','CV','CM','SZ','SD','S1','SR')
and SDLTTR > '579'
and SDNXTR > '619'
and SDSHAN > 500
;
let sub = $(sub) + 1;

loop

Do Loop

Miguel_Angel_Baeyens — Tue, 31 Jan 2012 22:03:58 GMT

Hi,

Use WHILE instead of WHEN. That should do.

Hope that helps.

Miguel

topic Do Loop in QlikView

Do Loop

Do Loop