Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

how to replace multiple field values with null.

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 

14 Replies
Highlighted
Anonymous
Not applicable

Re: how to replace multiple field values with null.

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;

Highlighted
Contributor III
Contributor III

Re: how to replace multiple field values with null.

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

Highlighted
Champion III
Champion III

Re: how to replace multiple field values with null.

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?

Highlighted
Contributor III
Contributor III

Re: how to replace multiple field values with null.

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.Dates.jpg

Highlighted
Anonymous
Not applicable

Re: how to replace multiple field values with null.

quiet easy, just try

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

Highlighted
Anonymous
Not applicable

Re: how to replace multiple field values with null.

or

date([Date_Start_Org]) as [Date_Start_Org]

Highlighted
Contributor III
Contributor III

Re: how to replace multiple field values with null.

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

Highlighted
Champion III
Champion III

Re: how to replace multiple field values with null.

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

Highlighted
Contributor III
Contributor III

Re: how to replace multiple field values with null.

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