Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
siva0606
Contributor III
Contributor III

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

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)



jagan
Luminary Alumni
Luminary Alumni

Hi Siva,

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

Regards,

Jagan.

Not applicable

Hi Siva,

How do u define a salary field in second table?

Pls explain..

siva0606
Contributor III
Contributor III
Author

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

siva0606
Contributor III
Contributor III
Author

Thank you jagan

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

siva0606
Contributor III
Contributor III
Author

Thank you vishwa

Not applicable

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)

jagan
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.

Not applicable

Hello,

Can you please explain the MonthDiff thing