how to replace multiple field values with null.

[shahfaisalq]

Hi every one,

i am at the beginner level of Qlik scripting. Need a little assistance.

down below is the script i used to load the data from the database.

[org]:

LOAD

[ID_FRS_Org] AS [ID_FRS_Link],

[Name_Org],

[Type_Org],

[Type_Org_Affiliation],

[Email_Org],

if(year(Date([Date_Start_Org]))=9000,Null(),Date([Date_Start_Org] )) AS [Date_Start_Org],

Date([Date_End_Org] ) AS [Date_End_Org],

[Aspect_Org],

[System_Org],

[ID_System_Org],

[Phone_Org],

[ID_State_Org];

SQL SELECT `ID_FRS_Org`,

`Name_Org`,

`Type_Org`,

`Type_Org_Affiliation`,

`Email_Org`,

`Date_Start_Org`,

`Date_End_Org`,

`Aspect_Org`,

`System_Org`,

`ID_System_Org`,

`Phone_Org`,

`ID_State_Org`

FROM `site_1.0_2018_05_03`.`org`;

Now i want to replace some specific date with null. i use  if(year(Date([Date_Start_Org]))=9000,Null(),Date([Date_Start_Org] )) AS [Date_Start_Org] this statement to replace 9000/12/31. Now i want to replace multiple dates with null instead of one.

what i have to do.

Kindly help me out here.

i will really appreciate your help and efforts.

Thanks,

Regards,

ShahFaisal Qayyum 

[vishsaggi]

Are you getting any error or you are getting additional irrelevant dates?

[shahfaisalq]

yes i getting an error on your defined script which say the year field is not exist. by the runing the Robin script the irrelevant dates are removed but the rest of the dates has been changed like in the image.

[vishsaggi]

Try this:

[org]:

LOAD

[ID_FRS_Org] AS [ID_FRS_Link],

[Name_Org],

[Type_Org],

[Type_Org_Affiliation],

[Email_Org],

YearFlg,

Date(Date#([Date_Start_Org], 'M/DD/YYYY' ), 'M/DD/YYYY') AS Date_Start_Org,

Date([Date_End_Org] ) AS [Date_End_Org],

[Aspect_Org],

[System_Org],

[ID_System_Org],

[Phone_Org],

[ID_State_Org]

WHERE YearFlg > 1950 AND YearFlg <= Year(Today());

SQL SELECT `ID_FRS_Org`,

`Name_Org`,

`Type_Org`,

`Type_Org_Affiliation`,

`Email_Org`,

YEAR(`Date_Start_Org`) AS YearFlg,

`Date_Start_Org`,

`Date_End_Org`,

`Aspect_Org`,

`System_Org`,

`ID_System_Org`,

`Phone_Org`,

`ID_State_Org`

FROM `site_1.0_2018_05_03`.`org`;

[shahfaisalq]

hi Vishwarath,

still the code is not working. I search on the internet and their is very limited data regarding to qlik sense scripting. But i think why not we use pick,match or replace function.

Now my question is either the above three mentioned function will work in my case.

Thanks.

Regards,

[Peter_Cammaert]

Please read this great treatment by HIC first: Get the Dates Right