Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Valued Contributor II

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
sebastianlettner
Contributor

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

3 Replies
paulyeo11
Valued Contributor II

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

my QV Doc

sebastianlettner
Contributor

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

paulyeo11
Valued Contributor II

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