topic Re: Record Number exists in Two categories in QlikView

Record Number exists in Two categories

Anonymous — Sun, 07 Jan 2018 07:52:42 GMT

Hello Experts,

I am facing one issue in report, The record number showing in different categories, Below is the sample,

Total	Female	Category of Insurees	Record Number	Joiner Type
1	1	21-30	20639	-
1	1	61-70	17006	-
1	1	100+	17006	-
1	1	100+	20639

In the above table rec no 20639 showing in the categories 21 - 30 and 100+ as well and my expression is

Count({$<_TransSubType={'CONTRACT','JOINERS'},_YearMonth={'$(vCurrentMonth)'},Record_TYPE_CODE={'1'},[Gender EN]={'Female'}>} DISTINCT RecordNumber),

The record should be in one category, How can I avoid this, Please advise me.

Thanks,

Ram

Re: Record Number exists in Two categories

petter — Sun, 07 Jan 2018 09:24:21 GMT

And your'e absolutely sure that you have correct categorization in your data?

Which of the coluns are dimensions and which are expressions?

Is it a straight table you are using?

Is the categorization done by hou in your script or is it from the sourcd data?

Re: Record Number exists in Two categories

Anonymous — Sun, 07 Jan 2018 09:54:14 GMT

Hi Petter,

Thank you for the reply,

i am sure that the categorization is correct

dimension is [Category of insured Persons] and Counting the Record Number along with few filter conditions.

Yes, It is straight table.

Categorization done by me and it is in the script not from the source.

Regards,

Ram

Re: Record Number exists in Two categories

petter — Sun, 07 Jan 2018 10:02:33 GMT

For me at least it is hard to see where the problem is without getting more information...

Is it possible for you to share a subset of your application that illustrates the problem?

Re: Record Number exists in Two categories

Anonymous — Sun, 07 Jan 2018 10:05:16 GMT

Hello Peter,

Application is very big, Here I am sharing the script for category . Please check it.Let me know if further information required

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])=1,Dual('1',1),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>1 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=10,Dual('2-10',2),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>10 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=20,Dual('11-20',3),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>20 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=30,Dual('21-30',4),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>30 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=40,Dual('31-40',5),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>40 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=50,Dual('41-50',6),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>50 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=60,Dual('51-60',7),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>60 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=70,Dual('61-70',8),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>70 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=80,Dual('71-80',9),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>80 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=90,Dual('81-90',10),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>90 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=100,Dual('91-100',11),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>100,Dual('100+',12),Dual('N/A',13))))))))))))) as [Category of Insured Persons],

Regards,

Ram

Re: Record Number exists in Two categories

Anonymous — Sun, 07 Jan 2018 10:16:40 GMT

Hello Peter,

First I defined the category with below script

If([Company Size Actual]=1,Dual('1',1),

If([Company Size Actual]>1 and [Company Size Actual]<=10,Dual('2-10',2),

If([Company Size Actual]>10 and [Company Size Actual]<=20,Dual('11-20',3),

If([Company Size Actual]>20 and [Company Size Actual]<=30,Dual('21-30',4),

If([Company Size Actual]>30 and [Company Size Actual]<=40,Dual('31-40',5),

If([Company Size Actual]>40 and [Company Size Actual]<=50,Dual('41-50',6),

If([Company Size Actual]>50 and [Company Size Actual]<=60,Dual('51-60',7),

If([Company Size Actual]>60 and [Company Size Actual]<=70,Dual('61-70',8),

If([Company Size Actual]>70 and [Company Size Actual]<=80,Dual('71-80',9),

If([Company Size Actual]>80 and [Company Size Actual]<=90,Dual('81-90',10),

If([Company Size Actual]>90 and [Company Size Actual]<=100,Dual('91-100',11),

If([Company Size Actual]>100,Dual('100+',12),Dual('N/A',13))))))))))))) as [Category of Insured Persons],

Then I used Concatenate function and concatenated with previously given script, Do you think Can I use Left join instead of Concatenate. Your analysis will help me to resolve the issue.

Regards,

Ram

Re: Record Number exists in Two categories

petter — Sun, 07 Jan 2018 10:46:12 GMT

So are you doing a concatenation of the tables?

Re: Record Number exists in Two categories

Anonymous — Sun, 07 Jan 2018 10:50:30 GMT

Yes, I did the concatenation because based on the company size the values will be append to respective category and, I have another concatenation for based on the payment the records will be append.

Regards,

Ram

Re: Record Number exists in Two categories

petter — Sun, 07 Jan 2018 11:20:16 GMT

Doing concatenation might lead to the issue of getting more than one category.

Re: Record Number exists in Two categories

Anonymous — Sun, 07 Jan 2018 11:23:06 GMT

Ok, Would you suggest me better function to resolve the issue.

Regards,

Ram

Re: Record Number exists in Two categories

petter — Mon, 08 Jan 2018 07:34:09 GMT

Could you share the name of the columns and the tables you are bringing in to see how it can be joined together or transformed in the right way?

Re: Record Number exists in Two categories

Anonymous — Mon, 08 Jan 2018 07:39:48 GMT

Hi Petter,

Thank you for the reply, Tables are big, Could you please send me a test email to my email id : rama79.putti@yahoo.com, so that I will share you.

Regards,

Ram

Re: Record Number exists in Two categories

petter — Mon, 08 Jan 2018 07:47:59 GMT

A representative sample from each would be enough actually...

Re: Record Number exists in Two categories

Anonymous — Mon, 08 Jan 2018 09:23:21 GMT

Hi Peter,

Please check the below script. This is link table script

TEMP_TABLE:

LOAD Distinct INSURANCE_NUMBER & '_' & COMPANY_NUMBER & '_' & RG_BRANCH_CODE & '_' & REGISTRATION_START_DATE AS %BI_RG_CONTRACT_KEY,

INSURANCE_NUMBER,// Added while doing testing

COMPANY_NUMBER,

RG_BRANCH_CODE,

REGISTRATION_START_DATE,

REGISTRATION_END_DATE,

REGISTRATION_CREATE_DATE,

RESIGNATION_CREATE_DATE,

If(Left(COMPANY_NUMBER,4)='1011',1,If(Match(Left(COMPANY_NUMBER,4),'1064','1124','1074','1054','1044','1084'),4,If(mid(COMPANY_NUMBER,4,1)='2',2,If(mid(COMPANY_NUMBER,4,1)='5',5,If(Left(COMPANY_NUMBER,4)='1013',3))))) as %INSURANCE_TYPE_CODE,

RESIGNATION_CODE,

RESIGNATION_BRANCH_CODE,

'CONTRACT' AS _TransType,

'CONTRACT' AS _TransSubType

FROM

[$(vQVDsFolder)BI_RG_CONTRACT.qvd]

(qvd);

Left Join (TEMP_TABLE)// to add birthday for each insured person in order to calculate the age

LOAD Distinct INSURANCE_NUMBER,

DATE(INSURER_BIRTHDAY,'DD/MM/YYYY') AS INSURER_BIRTHDAY

FROM

[$(vQVDsFolder)BI_RG_INSURER.qvd]

(qvd);

LINKTABLE_TEMP:

NoConcatenate

LOAD %BI_RG_CONTRACT_KEY,

COMPANY_NUMBER AS %COMPANY_NUMBER,

COMPANY_NUMBER AS [Company Number],

INSURANCE_NUMBER AS %INSURANCE_NUMBER,

INSURANCE_NUMBER AS [Insurance Number],

%INSURANCE_TYPE_CODE,

RG_BRANCH_CODE AS %RG_BRANCH_CODE,

RESIGNATION_CODE AS %RESIGNATION_CODE,

RESIGNATION_BRANCH_CODE AS %RESIGNATION_BRANCH_CODE,

INSURER_BIRTHDAY,

If(Num(MonthStart(REGISTRATION_START_DATE))<33786,33786,Floor(Num(MonthStart(REGISTRATION_START_DATE)))) AS START_DATE_TEMP,

Floor(Num(MonthStart(If(IsNull(REGISTRATION_END_DATE),AddMonths(Today(),1),If(Floor(REGISTRATION_END_DATE)=Floor(MonthEnd(REGISTRATION_END_DATE)) or (Year(REGISTRATION_START_DATE)=Year(REGISTRATION_END_DATE) and Month(REGISTRATION_START_DATE)=Month(REGISTRATION_END_DATE)),AddMonths(REGISTRATION_END_DATE,1),REGISTRATION_END_DATE))))) as END_DATE_TEMP,

If(Num(MonthStart(REGISTRATION_START_DATE))<33786,33786,Floor(Num(MonthStart(REGISTRATION_START_DATE)))) & '_' & Floor(Num(MonthStart(If(IsNull(REGISTRATION_END_DATE),AddMonths(Today(),1),If(Floor(REGISTRATION_END_DATE)=Floor(MonthEnd(REGISTRATION_END_DATE)) or (Year(REGISTRATION_START_DATE)=Year(REGISTRATION_END_DATE) and Month(REGISTRATION_START_DATE)=Month(REGISTRATION_END_DATE)),AddMonths(REGISTRATION_END_DATE,1),REGISTRATION_END_DATE))))) as TEMP_KEY,

_TransType,

_TransSubType

Resident TEMP_TABLE

where Floor(REGISTRATION_START_DATE) <= If(IsNull(REGISTRATION_END_DATE),Today(),Floor(REGISTRATION_END_DATE)) and num(If(IsNull(REGISTRATION_END_DATE),Today(),REGISTRATION_END_DATE))>=33786; // 33786 is 1/july/1992 which is day one in PASI as a organization

Left Join(LINKTABLE_TEMP)// by using while statement, this script will generate all the periods between start date and end date for each insured person

LOAD Distinct TEMP_KEY,

If(Len(Num(Month(AddMonths(START_DATE_TEMP,IterNo()-1))))=1,(Year(AddMonths(START_DATE_TEMP,IterNo()-1)) & '0' & Num(Month(AddMonths(START_DATE_TEMP,IterNo()-1)))),Year(AddMonths(START_DATE_TEMP,IterNo()-1)) & Num(Month(AddMonths(START_DATE_TEMP,IterNo()-1)))) as _YearMonth,

Resident LINKTABLE_TEMP

While (MonthStart(END_DATE_TEMP-1) - AddMonths(START_DATE_TEMP,IterNo()-1))+1 >0;

Left Join (LINKTABLE_TEMP)// to add count of insured persons in each company

LOAD Distinct _YearMonth,

%COMPANY_NUMBER,

Count(DISTINCT %INSURANCE_NUMBER) as [Company Size Actual]

Resident LINKTABLE_TEMP

Group By _YearMonth,%COMPANY_NUMBER;

LINKTABLE:

NoConcatenate

LOAD Distinct *,

1 as _DateTypeTemp1,//1=Actual, 2=System, 0=Both

Age(MonthEnd(Date#(_YearMonth,'YYYYMM')),INSURER_BIRTHDAY) as Age,// Age calculation

Age(MonthEnd(START_DATE_TEMP),INSURER_BIRTHDAY) as [Age on Registeration],

%INSURANCE_NUMBER & '_' & %COMPANY_NUMBER & '_' & _YearMonth as SALARY_CHANGE_TMEP_KEY,

%INSURANCE_NUMBER & '_' & _YearMonth as %INSURENCE_PRINT_CARD_KEY

Resident LINKTABLE_TEMP;

DROP Table LINKTABLE_TEMP;

CONTRACT_TEMP:

NoConcatenate

LOAD Distinct %BI_RG_CONTRACT_KEY,

COMPANY_NUMBER AS %COMPANY_NUMBER,

COMPANY_NUMBER AS [Company Number],

INSURANCE_NUMBER AS %INSURANCE_NUMBER,

INSURANCE_NUMBER AS [Insurance Number],

%INSURANCE_TYPE_CODE,

RG_BRANCH_CODE AS %RG_BRANCH_CODE,

RESIGNATION_CODE AS %RESIGNATION_CODE,

RESIGNATION_BRANCH_CODE AS %RESIGNATION_BRANCH_CODE,

INSURER_BIRTHDAY,

If(Num(MonthStart(REGISTRATION_CREATE_DATE))<33786,33786,Floor(Num(MonthStart(REGISTRATION_CREATE_DATE)))) AS START_DATE_TEMP,

Floor(Num(MonthStart(If(IsNull(RESIGNATION_CREATE_DATE),AddMonths(Today(),1),If(Floor(RESIGNATION_CREATE_DATE)=Floor(MonthEnd(RESIGNATION_CREATE_DATE)) or (Year(REGISTRATION_CREATE_DATE)=Year(RESIGNATION_CREATE_DATE) and Month(REGISTRATION_CREATE_DATE)=Month(RESIGNATION_CREATE_DATE)),AddMonths(RESIGNATION_CREATE_DATE,1),RESIGNATION_CREATE_DATE))))) as END_DATE_TEMP,

If(Num(MonthStart(REGISTRATION_CREATE_DATE))<33786,33786,Floor(Num(MonthStart(REGISTRATION_CREATE_DATE)))) & '_' & Floor(Num(MonthStart(If(IsNull(RESIGNATION_CREATE_DATE),AddMonths(Today(),1),If(Floor(RESIGNATION_CREATE_DATE)=Floor(MonthEnd(RESIGNATION_CREATE_DATE)) or (Year(REGISTRATION_CREATE_DATE)=Year(RESIGNATION_CREATE_DATE) and Month(REGISTRATION_CREATE_DATE)=Month(RESIGNATION_CREATE_DATE)),AddMonths(RESIGNATION_CREATE_DATE,1),RESIGNATION_CREATE_DATE))))) as TEMP_KEY,

_TransType,

_TransSubType

Resident TEMP_TABLE

where Floor(REGISTRATION_CREATE_DATE) <= If(IsNull(RESIGNATION_CREATE_DATE),Today(),Floor(RESIGNATION_CREATE_DATE)) and num(If(IsNull(RESIGNATION_CREATE_DATE),Today(),RESIGNATION_CREATE_DATE))>=33786;

Left Join(CONTRACT_TEMP)// same step as above but here we use system date, above we use the actual date

LOAD Distinct TEMP_KEY,

Resident CONTRACT_TEMP

While (MonthStart(END_DATE_TEMP-1) - AddMonths(START_DATE_TEMP,IterNo()-1))+1 >0;

Left Join (CONTRACT_TEMP)

LOAD Distinct _YearMonthSystem,

%COMPANY_NUMBER,

Count(DISTINCT %INSURANCE_NUMBER) as [Company Size System]

Resident CONTRACT_TEMP

Group By _YearMonthSystem,%COMPANY_NUMBER;

Left Join (CONTRACT_TEMP)

LOAD Distinct %BI_RG_CONTRACT_KEY,_YearMonth as _YearMonthSystem,1 as Flag

Resident LINKTABLE;

Left Join(LINKTABLE)

LOAD Distinct %BI_RG_CONTRACT_KEY,_YearMonthSystem as _YearMonth,2 as _DateTypeTemp2,[Company Size System]

Resident CONTRACT_TEMP;

Concatenate(LINKTABLE)

LOAD Distinct*,_YearMonthSystem as _YearMonth,

2 as _DateTypeTemp1,

Age(MonthEnd(Date#(_YearMonthSystem,'YYYYMM')),INSURER_BIRTHDAY) as Age,// Age calculation

Age(MonthEnd(START_DATE_TEMP),INSURER_BIRTHDAY) as [Age on Registeration],

%INSURANCE_NUMBER & '_' & %COMPANY_NUMBER & '_' & _YearMonthSystem as SALARY_CHANGE_TMEP_KEY,

%INSURANCE_NUMBER & '_' & _YearMonthSystem as %INSURANCE_PRINT_CARD_KEY

Resident CONTRACT_TEMP

Where IsNull(Flag);

DROP Table CONTRACT_TEMP;

DROP Fields TEMP_KEY,START_DATE_TEMP,END_DATE_TEMP,INSURER_BIRTHDAY,Flag From LINKTABLE;

////////////////// Leavers Script Start /////////////////////////////

LEAVERS:

NoConcatenate

LOAD Distinct %BI_RG_CONTRACT_KEY,

COMPANY_NUMBER AS %COMPANY_NUMBER,

COMPANY_NUMBER AS [Company Number],

INSURANCE_NUMBER AS %INSURANCE_NUMBER,

INSURANCE_NUMBER AS [Insurance Number],

%INSURANCE_TYPE_CODE,

RG_BRANCH_CODE AS %RG_BRANCH_CODE,

RESIGNATION_CODE AS %RESIGNATION_CODE,

RESIGNATION_BRANCH_CODE AS %RESIGNATION_BRANCH_CODE,

INSURER_BIRTHDAY,

Age(REGISTRATION_END_DATE,INSURER_BIRTHDAY) as Age,

Age(REGISTRATION_START_DATE,INSURER_BIRTHDAY) as [Age on Registeration],

INSURANCE_NUMBER & '_' & COMPANY_NUMBER & '_' & If(Len(Num(Month(REGISTRATION_END_DATE)))=1,Year(REGISTRATION_END_DATE) & '0' & Num(Month(REGISTRATION_END_DATE)),Year(REGISTRATION_END_DATE) & Num(Month(REGISTRATION_END_DATE))) as SALARY_CHANGE_TMEP_KEY,

If(Len(Num(Month(REGISTRATION_END_DATE)))=1,Year(REGISTRATION_END_DATE) & '0' & Num(Month(REGISTRATION_END_DATE)),Year(REGISTRATION_END_DATE) & Num(Month(REGISTRATION_END_DATE))) & '_' & INSURANCE_NUMBER & '_' & 0 as %INSURED_AGE_KEY,

If(Len(Num(Month(REGISTRATION_END_DATE)))=1,Year(REGISTRATION_END_DATE) & '0' & Num(Month(REGISTRATION_END_DATE)),Year(REGISTRATION_END_DATE) & Num(Month(REGISTRATION_END_DATE))) as _YearMonth,

0 as _DateTypeTemp1,

'Leaver' as [Joiner Type EN],//by Bashar

'مغادر' as [Joiner Type AR],//by Bashar

'LEAVERS' as _TransSubType,

'CONTRACT' AS _TransType

Resident TEMP_TABLE

Where REGISTRATION_END_DATE>=33786 and not IsNull(REGISTRATION_END_DATE) and Year(REGISTRATION_END_DATE)=Year(RESIGNATION_CREATE_DATE) and Month(REGISTRATION_END_DATE)=Month(RESIGNATION_CREATE_DATE);

Left Join (LEAVERS)// to add count of insured persons in each company

LOAD Distinct _YearMonth,

%COMPANY_NUMBER,

Count(DISTINCT %INSURANCE_NUMBER) as [Company Size Actual]

Resident LEAVERS

Group By _YearMonth,%COMPANY_NUMBER;

Concatenate(LINKTABLE)

LOAD *

Resident LEAVERS;

DROP TABLE LEAVERS;

LEAVERS:

NoConcatenate

LOAD Distinct %BI_RG_CONTRACT_KEY,

COMPANY_NUMBER AS %COMPANY_NUMBER,

COMPANY_NUMBER AS [Company Number],

INSURANCE_NUMBER AS %INSURANCE_NUMBER,

INSURANCE_NUMBER AS [Insurance Number],

%INSURANCE_TYPE_CODE,

RG_BRANCH_CODE AS %RG_BRANCH_CODE,

RESIGNATION_CODE AS %RESIGNATION_CODE,

RESIGNATION_BRANCH_CODE AS %RESIGNATION_BRANCH_CODE,

Age(RESIGNATION_CREATE_DATE,INSURER_BIRTHDAY) as Age,

INSURER_BIRTHDAY,

Age(REGISTRATION_CREATE_DATE,INSURER_BIRTHDAY) as [Age on Registeration],

INSURANCE_NUMBER & '_' & COMPANY_NUMBER & '_' & If(Len(Num(Month(RESIGNATION_CREATE_DATE)))=1,Year(RESIGNATION_CREATE_DATE) & '0' & Num(Month(RESIGNATION_CREATE_DATE)),Year(RESIGNATION_CREATE_DATE) & Num(Month(RESIGNATION_CREATE_DATE))) as SALARY_CHANGE_TMEP_KEY,

If(Len(Num(Month(RESIGNATION_CREATE_DATE)))=1,Year(RESIGNATION_CREATE_DATE) & '0' & Num(Month(RESIGNATION_CREATE_DATE)),Year(RESIGNATION_CREATE_DATE) & Num(Month(RESIGNATION_CREATE_DATE))) & '_' & INSURANCE_NUMBER & '_' & 2 as %INSURED_AGE_KEY,

2 as _DateTypeTemp1,

'Leaver' as [Joiner Type EN],//by Bashar

'مغادر' as [Joiner Type AR],//by Bashar

'LEAVERS' as _TransSubType,

'CONTRACT' AS _TransType

Resident TEMP_TABLE

Where RESIGNATION_CREATE_DATE>=33786 and not IsNull(RESIGNATION_CREATE_DATE) and (Year(REGISTRATION_END_DATE)<>Year(RESIGNATION_CREATE_DATE) or Month(REGISTRATION_END_DATE)<>Month(RESIGNATION_CREATE_DATE));

Left Join (LEAVERS)// to add count of insured persons in each company

LOAD Distinct _YearMonth,

%COMPANY_NUMBER,

Count(DISTINCT %INSURANCE_NUMBER) as [Company Size System]

Resident LEAVERS

Group By _YearMonth,%COMPANY_NUMBER;

Concatenate(LINKTABLE)

LOAD *

Resident LEAVERS;

DROP TABLE LEAVERS;

////////////////// Leavers Script End /////////////////////////////

JOINERS:

NoConcatenate

LOAD Distinct %BI_RG_CONTRACT_KEY,

COMPANY_NUMBER AS %COMPANY_NUMBER,

COMPANY_NUMBER AS [Company Number],

INSURANCE_NUMBER AS %INSURANCE_NUMBER,

INSURANCE_NUMBER AS [Insurance Number],

%INSURANCE_TYPE_CODE,

RG_BRANCH_CODE AS %RG_BRANCH_CODE,

RESIGNATION_CODE AS %RESIGNATION_CODE,

RESIGNATION_BRANCH_CODE AS %RESIGNATION_BRANCH_CODE,

INSURER_BIRTHDAY,

Age(REGISTRATION_START_DATE,INSURER_BIRTHDAY) as Age,

Age(REGISTRATION_START_DATE,INSURER_BIRTHDAY) as [Age on Registeration],

INSURANCE_NUMBER & '_' & COMPANY_NUMBER & '_' & If(Len(Num(Month(REGISTRATION_START_DATE)))=1,Year(REGISTRATION_START_DATE) & '0' & Num(Month(REGISTRATION_START_DATE)),Year(REGISTRATION_START_DATE) & Num(Month(REGISTRATION_START_DATE))) as SALARY_CHANGE_TMEP_KEY,

If(Len(Num(Month(REGISTRATION_START_DATE)))=1,Year(REGISTRATION_START_DATE) & '0' & Num(Month(REGISTRATION_START_DATE)),Year(REGISTRATION_START_DATE) & Num(Month(REGISTRATION_START_DATE))) & '_' & INSURANCE_NUMBER & '_' & 1 as %INSURED_AGE_KEY,

1 as _DateTypeTemp1,

If(INSURANCE_NUMBER=Previous(INSURANCE_NUMBER),1,0) as _Rejoiner,

If(INSURANCE_NUMBER=Previous(INSURANCE_NUMBER),'Rejoiner','New Joiner') as [Joiner Type EN],//by Bashar

If(INSURANCE_NUMBER=Previous(INSURANCE_NUMBER),'اعادة التحاق','مشترك جديد') as [Joiner Type AR],//by Bashar

'JOINERS' as _TransSubType,

'CONTRACT' AS _TransType

Resident TEMP_TABLE

Where REGISTRATION_START_DATE>=33786 and not IsNull(REGISTRATION_START_DATE) //and (Year(REGISTRATION_START_DATE)<>Year(REGISTRATION_CREATE_DATE) or Month(REGISTRATION_START_DATE)<>Month(REGISTRATION_CREATE_DATE))

Order By INSURANCE_NUMBER,REGISTRATION_START_DATE;

Left Join (JOINERS)// to add count of insured persons in each company

LOAD Distinct _YearMonth,

%COMPANY_NUMBER,

Count(DISTINCT %INSURANCE_NUMBER) as [Company Size Actual]

Resident JOINERS

Group By _YearMonth,%COMPANY_NUMBER;

//Concatenate(LINKTABLE)

Left Join(LINKTABLE)

LOAD *

Resident JOINERS;

DROP TABLE JOINERS;

JOINERS:

NoConcatenate

LOAD Distinct %BI_RG_CONTRACT_KEY,

COMPANY_NUMBER AS %COMPANY_NUMBER,

COMPANY_NUMBER AS [Company Number],

INSURANCE_NUMBER AS %INSURANCE_NUMBER,

INSURANCE_NUMBER AS [Insurance Number],

%INSURANCE_TYPE_CODE,

RG_BRANCH_CODE AS %RG_BRANCH_CODE,

RESIGNATION_CODE AS %RESIGNATION_CODE,

RESIGNATION_BRANCH_CODE AS %RESIGNATION_BRANCH_CODE,

INSURER_BIRTHDAY,

Age(REGISTRATION_CREATE_DATE,INSURER_BIRTHDAY) as Age,

Age(REGISTRATION_CREATE_DATE,INSURER_BIRTHDAY) as [Age on Registeration],

INSURANCE_NUMBER & '_' & COMPANY_NUMBER & '_' & If(Len(Num(Month(REGISTRATION_CREATE_DATE)))=1,Year(REGISTRATION_CREATE_DATE) & '0' & Num(Month(REGISTRATION_CREATE_DATE)),Year(REGISTRATION_CREATE_DATE) & Num(Month(REGISTRATION_CREATE_DATE))) as SALARY_CHANGE_TMEP_KEY,

If(Len(Num(Month(REGISTRATION_CREATE_DATE)))=1,Year(REGISTRATION_CREATE_DATE) & '0' & Num(Month(REGISTRATION_CREATE_DATE)),Year(REGISTRATION_CREATE_DATE) & Num(Month(REGISTRATION_CREATE_DATE))) & '_' & INSURANCE_NUMBER & '_' & 2 as %INSURED_AGE_KEY,

2 as _DateTypeTemp1,

If(INSURANCE_NUMBER=Previous(INSURANCE_NUMBER),1,0) as _Rejoiner,

If(INSURANCE_NUMBER=Previous(INSURANCE_NUMBER),'Rejoiner','New Joiner') as [Joiner Type EN],//by Bashar

If(INSURANCE_NUMBER=Previous(INSURANCE_NUMBER),'اعادة التحاق','مشترك جديد') as [Joiner Type AR],//by Bashar

'JOINERS' as _TransSubType,

'CONTRACT' AS _TransType

Resident TEMP_TABLE

Where REGISTRATION_CREATE_DATE>=33786 and not IsNull(REGISTRATION_CREATE_DATE) //and (Year(REGISTRATION_START_DATE)<>Year(REGISTRATION_CREATE_DATE) or Month(REGISTRATION_START_DATE)<>Month(REGISTRATION_CREATE_DATE))

Order By INSURANCE_NUMBER,REGISTRATION_CREATE_DATE;

Left Join (JOINERS)// to add count of insured persons in each company

LOAD Distinct _YearMonth,

%COMPANY_NUMBER,

Count(DISTINCT %INSURANCE_NUMBER) as [Company Size System]

Resident JOINERS

Group By _YearMonth,%COMPANY_NUMBER;

Left Join(LINKTABLE)

//Concatenate(LINKTABLE)

LOAD *

Resident JOINERS;

DROP TABLE JOINERS;

////////////////// New Joiner/Rejoin Script End /////////////////////////////

DROP Table TEMP_TABLE;

STORE LINKTABLE into [$(vQVDsFolder)LINKTABLE_STEP1.qvd];

DROP Table LINKTABLE;

Re: Record Number exists in Two categories

Anonymous — Wed, 10 Jan 2018 09:22:31 GMT

Hello Petter,

The issue is critical, Could you please suggest me if you have any solution. please find the attached script.