Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

siva0606
New Contributor II

Autogenerate?

Hi all,

I have the table like this,

EmployeeNo

FromDate

ToDate

Salary

123

1/1/2011 

1/7/2011

3000

123

1/8/2011 

1/7/2012

5000

Now i want to create table like this,

EmployeeNo

Year

Month

Salary

123

2011

Jan

3000

123

2011

Feb

3000

123

2011

Mar

3000

123

2011

Apr

3000

123

2011

May

3000

123

2011

Jun

3000

123

2011

Jul

3000

123

2011

Aug

3000

123

2011

Sep

3000

123

2011

Oct

3000

123

2011

Nov

3000

123

2011

Dec

3000

123

2012

Jan

5000

123

2012

Feb

5000

123

2012

Mar

5000

123

2012

Apr

5000

123

2012

May

5000

123

2012

Jun

5000

I have hundreds of employee numbers. Kindly all help me to solve this problem.

Thanks and Regards,

Sivasu

11 Replies
Not applicable

Re: Autogenerate?

hi  siva

use it,

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07;

this function seperate the string.

or       if you follow these then you can understand easily

index( 'abcdefg', 'cd' ) returns 3

index( 'abcdabcd', 'b', 2 ) returns 6

index( 'abcdabcd', 'b', -2 ) returns 2

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

see the attached image for seperate date(ie seperate string)



MVP
MVP

Re: Autogenerate?

Hi Siva,

Please find attached file for solution, hope it helps you.

Regards,

Jagan.

Not applicable

Re: Autogenerate?

Hi Siva,

How do u define a salary field in second table?

Pls explain..

siva0606
New Contributor II

Re: Autogenerate?

Sorry yaar my second table is wrong. 3000 should be replace by 5000 from Aug2011 to dec 2011.

siva0606
New Contributor II

Re: Autogenerate?

Thank you jagan

But i have hundreds of employee numbers. wat to do?

siva0606
New Contributor II

Re: Autogenerate?

Thank you vishwa

Not applicable

Re: Autogenerate?

Directory

;

LOAD

*,

Month(SplitDate) AS Month,

Year(SplitDate) AS Year;

LOAD

*,

AddMonths(FromDate, IterNo() -1) AS SplitDate

WHILE IterNo() < NumSum($(MonthDiff(FromDate, ToDate)),  1);     

LOAD

EmployeeNo,

Date(Date#(FromDate, 'D/M/YYYY')) AS FromDate,

Date(Date#(ToDate, 'D/M/YYYY')) AS ToDate,

Salary;

LOAD * INLINE [

EmployeeNo,FromDate,ToDate,Salary

123,1/1/2011,1/7/2011,3000

123,1/8/2011,1/7/2012,5000

]
;

Instead of this

LOAD * INLINE [

EmployeeNo,FromDate,ToDate,Salary

123,1/1/2011,1/7/2011,3000

123,1/8/2011,1/7/2012,5000

]
;

u can load ur table whatever it may be(Qvd, Select statement,xml,etc)

MVP
MVP

Re: Autogenerate?

Hi,

It works for any number of employees, the logic is same.  I used this logic for 10 lakh records.

Regards,

Jagan.

Not applicable

Re: Autogenerate?

Hello,

Can you please explain the MonthDiff thing

Community Browser