Set Variable from fieldname

Mon, 06 Feb 2012 21:59:28 GMT

I'm tying to set a variable $(ToDate) to the value contained in a filedname (EndDate) so I can use it in a subsequent load SQL statement. I tried using the peek statement but I believe that can only be used in the load statement. Any ideas? Thanks.

DatePatternTo:
Load
if ($(sub) = 1, CDD01J ,
if ($(sub) = 2, CDD02J ,
if ($(sub) = 3, CDD03J ,
if ($(sub) = 4, CDD04J ,
if ($(sub) = 5, CDD05J ,
if ($(sub) = 6, CDD06J ,
if ($(sub) = 7, CDD07J ,
if ($(sub) = 8, CDD08J ,
if ($(sub) = 9, CDD09J ,
if ($(sub) = 10, CDD10J ,
if ($(sub) = 11, CDD11J ,
if ($(sub) = 12, CDD12J )))))))))))) as EndDate
;
SQL SELECT CDFY,CDDFYJ,CDD01J,CDD02J,CDD03J,CDD04J,CDD05J,CDD06J,CDD07J,CDD08J,CDD09J,CDD10J,CDD11J,CDD12J
FROM "JDE_PRODUCTION".PRODDTA.F0008 where CDFY = $(PrevYear2) and CDDTPN = 'R';

Set $(ToDate) = EndDate;

SalesDetails:

Load

'N/A' as Week,

'N/A' as Day,

'N/A' as WeekDay,

'N/A' as [Week Ending Date],

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 >= $(FromDate) and SDIVD <= $(ToDate)

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

Set Variable from fieldname

Mon, 06 Feb 2012 23:00:16 GMT

plz try peek function to get end date vaue and store that in a variable.

hope this will help ...

Set Variable from fieldname

hic — Mon, 06 Feb 2012 23:06:51 GMT

The peek function can be used outside Load statements, typically in a Let statement, e.g. Let $(vToDate) = peek(...); The problem you face is to choose which record to use - you most likely have several end dates in the table. Try something similar to the following (just one value):

MaxDate:

Load Max(Date) as EndDate resident FirstTable ;

Let vToDate = peek('EndDate',-1,'MaxDate');

Further, you should probably look at the Crosstable prefix to collapse your CDD01J .. CDD12J columns into one column.

topic Set Variable from fieldname in QlikView

Set Variable from fieldname

Set Variable from fieldname

Set Variable from fieldname