topic Re: Fill Table in QlikView

Fill Table

insurfacto — Sat, 16 Nov 2024 03:15:48 GMT

Hi Everybody
I have a question, this is my scenario:

In my chart table I need to fill rows with information missing

Original

Moth Salary
ENE
FEB
MAR
ABR $22,000.00
MAY
JUN
JUL
AGO
SEP
OCT
NOV $24,200.00
DIC

Target

Moth Salary
ENE $22,000.00
FEB $22,000.00
MAR $22,000.00
ABR $22,000.00
MAY $22,000.00
JUN $22,000.00
JUL $22,000.00
AGO $22,000.00
SEP $22,000.00
OCT $22,000.00
NOV $24,200.00
DIC $24,200.00

As you can see the information missing should be filled with previous information

Thanks!

Re: Fill Table

Oleg_Troyansky — Wed, 19 Jun 2019 00:15:35 GMT

Look at the functions Above(), Below(), Before(), and After(), perhaps you can use them.

However, how do you know what the salary was in Jan, Feb, and March - it could have been 20,000 ? You don't have any previous value to base your decision on...

Cheers,

Oleg Troyansky

Re: Fill Table

jonathandienst — Wed, 19 Jun 2019 05:51:47 GMT

Assuming this is for the load script, and you want to back fill the first few months with the first known value, you could use something like:

Input: LOAD *, RowNo() as MonthNum INLINE [ Month, Salary ENE FEB MAR ABR $22,000.00 MAY JUN JUL AGO SEP OCT NOV $24,200.00 DIC ]; // Fill down Temp: NoConcatenate LOAD Month, If(Len(Salary) = 0, Peek(Salary), Salary) as Salary, MonthNum Resident Input Order by MonthNum; // Back fill up Result: NoConcatenate LOAD Month, If(Len(Salary) = 0, Peek(Salary), Salary) as Salary, MonthNum Resident Input Order by MonthNum DESC;

Re: Fill Table

insurfacto — Thu, 20 Jun 2019 16:09:14 GMT

This solution worked but now I have changed the requirement with the following rules:

There is a table with this record.

1. Consider the date Start_date as labor start or start of month
2. If there are no changes in the salary since we started work to date / Current month with the same salary
3. If the employee has a salary change in the same month, a record must be generated with the same results with the same month
4. If the employee changes roles but retains the same salary as a new record
5. If the employee was discharged and returned to the company should generate only the month / year record in working life, then start again to fill
the table with the records since its new start

Example