topic Re: Autogenerate? in QlikView

Autogenerate?

siva0606 — Fri, 21 Dec 2012 05:24:28 GMT

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

Re: Autogenerate?

Fri, 21 Dec 2012 05:59:22 GMT

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)

Re: Autogenerate?

jagan — Fri, 21 Dec 2012 06:37:06 GMT

Hi Siva,

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

Regards,

Jagan.

Re: Autogenerate?

Fri, 21 Dec 2012 06:51:21 GMT

Hi Siva,

How do u define a salary field in second table?

Pls explain..

Re: Autogenerate?

siva0606 — Fri, 21 Dec 2012 06:57:24 GMT

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

Re: Autogenerate?

siva0606 — Fri, 21 Dec 2012 06:59:26 GMT

Thank you jagan

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

Re: Autogenerate?

siva0606 — Fri, 21 Dec 2012 06:59:51 GMT

Thank you vishwa

Re: Autogenerate?

Fri, 21 Dec 2012 07:09:19 GMT

Re: Autogenerate?

jagan — Fri, 21 Dec 2012 07:21:18 GMT

Hi,

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

Regards,

Jagan.

Re: Autogenerate?

Fri, 21 Dec 2012 07:42:17 GMT

Hello,

Can you please explain the MonthDiff thing

Re: Autogenerate?

jagan — Fri, 21 Dec 2012 07:47:04 GMT

Hi Priya,

Please find my explanation below, hope I clearly explained.

//Function to calculate number of Months Difference between first and second data ($1) refers first and ($2) refers second date parameter

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

Directory;

LOAD

Month(SplitDate) AS Month,

Year(SplitDate) AS Year;

//Using While here iterating every record to create (ToDate - FromDate) number of months difference, IterNo() keeps track of Iteration number of each record

LOAD

AddMonths(FromDate, IterNo() -1) AS SplitDate //New date field to get incremental date for every month

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

//Converting date format in this step

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

];

Regards,

Jagan.

Re: Autogenerate?

Fri, 21 Dec 2012 07:55:56 GMT

Thank You Very Much