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 

[Anonymous]

Hi, I suppose, you have a table with the dates you want to set null() like that:

SET DateFormat='YYYY/MM/DD';

TEMP:
LOAD * INLINE [
SETNULLDATES
2015/12/31
2016/12/31
2017/12/31
];

then you could use your script:

[org]:

LOAD

[ID_FRS_Org] AS [ID_FRS_Link],

[Name_Org],

[Type_Org],

[Type_Org_Affiliation],

[Email_Org],

if(Exists('SETNULLDATES',[Date_Start_Org]),

     null(),

     [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`;

drop table TEMP;

[shahfaisalq]

yes i want to replace every inappropriate date with null but only by year not with month and day.

like i write 9000 in the query and it is replaced with null. i just want to enter the year in the script because there are too many different months and days in every inappropriate year.

Thanks,

Regards

[vishsaggi]

How many different dates you want to convert to Nulls? Any specific reason you want to convert to nulls rather do not pull those dates into your QV if they do not help? Can you show some sample data with an example?

[shahfaisalq]

i used this script to remove 9000/12/31 date

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

now i want to remove  the 0001 and etc, if you see the empty area that is the 9000/12/31 date which has been removed by the above script which i use. now i want to remove those multiple dates but by year. not mentioning months and days.

[Anonymous]

quiet easy, just try

date(date#([Date_Start_Org]​,'YYYY/MM/DD')) as [Date_Start_Org]​

[Anonymous]

or

date([Date_Start_Org]) as [Date_Start_Org]

[shahfaisalq]

hi Robin,

I am at the very beginner level of Qlik scripting . Please write the whole query because i don't get it and  i will really appreciate you efforts .

Thanks,

Regards

[vishsaggi]

May be try this?

I have given the YearFlg as 1950 but you can give which ever is your min date your source has.

[org]:

LOAD

[ID_FRS_Org] AS [ID_FRS_Link],

[Name_Org],

[Type_Org],

[Type_Org_Affiliation],

[Email_Org],

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]

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]

i try your given script but its not working  by the way i am using mysql database with Qlik sense desktop.