topic Re: load script if [Reporting Code] < 00500 use date and [Reporting Code] > 00500 use date1 in QlikView

load script if [Reporting Code] < 00500 use date and [Reporting Code] > 00500 use date1

paulyeo11 — Thu, 26 May 2016 06:17:31 GMT

Hi All

Below is my simple QV doc script :-

SET vDevelopment = 0;

IF $(vDevelopment) = 0 THEN

SET vRAWPath = 'D:\AUTO\'; //local folder

SET vFile50 = 'FS_ADL';

else

END IF

GL_TABLE:

LOAD

'TDSS' as SOURCE_GL,

'60' as SOURCE,

date#(MakeDate(@53:57T, @50:52T ,@58:59T),'DD/MM/YYYY') as date,

makedate(@53:57T,@50:52T,1) as date1,

AutoNumberHash128(@58:77T,'60') As Link_GL_Key,

@124:129T as [Reporting Code],

if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]

FROM $(vRAWPath)$(vFile50).txt (ansi, fix, no labels, header is 0, record is line);

TABLE_P:

left keep (GL_TABLE)

load [Reporting Code],

If([Reporting Code]>=1 and [Reporting Code]<=19,'rEVENUE') as [rEVENUE]

resident GL_TABLE;

My requirement is to create a new feild name = date2 , the condition as below :-

field = date for reporting code 10000 - 99999

field = date1 for reporting code 00001 - 00500

Can some one advise me how to write the expression ?

Paul

Re: load script if [Reporting Code] < 00500 use date and [Reporting Code] > 00500 use date1

paulyeo11 — Thu, 26 May 2016 06:18:31 GMT

my QV Doc

Re: load script if [Reporting Code] < 00500 use date and [Reporting Code] > 00500 use date1

sebastianlettner — Tue, 31 May 2016 15:39:07 GMT

Hi,

try this Code

GL_TABLE:

LOAD

If(num([Reporting Code]) >= 10000 and num([Reporting Code]) <=99999,

date,

if(num([Reporting Code]) >=1 and num([Reporting Code]) <= 500,

date1,

'No Date field for Reporting Code: ' & [Reporting Code]

)

) as date2;

LOAD

'TDSS' as SOURCE_GL,

'60' as SOURCE,

date#(MakeDate(@53:57T, @50:52T ,@58:59T),'DD/MM/YYYY') as date,

makedate(@53:57T,@50:52T,1) as date1,

AutoNumberHash128(@58:77T,'60') As Link_GL_Key,

@124:129T as [Reporting Code],

if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]

FROM $(vRAWPath)$(vFile50).txt (ansi, fix, no labels, header is 0, record is line);

Regards

Sebastian Lettner

Re: load script if [Reporting Code] < 00500 use date and [Reporting Code] > 00500 use date1

paulyeo11 — Tue, 31 May 2016 20:40:29 GMT

Hi sir

In my actual application , I have to read 4 set similar data for different country. Meaning I need to cocationate .

Can you tell me your this approach is it same as partial Load ?

Paul

Sent from my iPhone