Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
paulyeo11
Valued Contributor III

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

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

1 Solution

Accepted Solutions

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

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

View solution in original post

3 Replies
paulyeo11
Valued Contributor III

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

my QV Doc

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

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

View solution in original post

paulyeo11
Valued Contributor III

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

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