topic Re: how to replace multiple field values with null. in QlikView

how to replace multiple field values with null.

shahfaisalq — Wed, 16 May 2018 14:04:37 GMT

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

Re: how to replace multiple field values with null.

Anonymous — Wed, 16 May 2018 18:55:38 GMT

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;

Re: how to replace multiple field values with null.

shahfaisalq — Wed, 16 May 2018 19:07:58 GMT

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

Re: how to replace multiple field values with null.

vishsaggi — Wed, 16 May 2018 19:09:05 GMT

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?

Re: how to replace multiple field values with null.

shahfaisalq — Wed, 16 May 2018 19:25:53 GMT

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.

Re: how to replace multiple field values with null.

Anonymous — Wed, 16 May 2018 19:40:16 GMT

quiet easy, just try

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

Re: how to replace multiple field values with null.

Anonymous — Wed, 16 May 2018 19:41:38 GMT

date([Date_Start_Org]) as [Date_Start_Org]

Re: how to replace multiple field values with null.

shahfaisalq — Wed, 16 May 2018 19:52:03 GMT

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

Re: how to replace multiple field values with null.

vishsaggi — Wed, 16 May 2018 19:54:17 GMT

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

Re: how to replace multiple field values with null.

shahfaisalq — Wed, 16 May 2018 20:21:35 GMT

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

Re: how to replace multiple field values with null.

vishsaggi — Wed, 16 May 2018 20:52:12 GMT

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

Re: how to replace multiple field values with null.

shahfaisalq — Wed, 16 May 2018 21:02:38 GMT

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.

Re: how to replace multiple field values with null.

vishsaggi — Wed, 16 May 2018 21:20:18 GMT

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

Re: how to replace multiple field values with null.

shahfaisalq — Thu, 17 May 2018 07:23:27 GMT

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,

Re: how to replace multiple field values with null.

Peter_Cammaert — Thu, 17 May 2018 09:58:02 GMT

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