Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
insurfacto
New Contributor

Fill Table

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!

Labels (1)
2 Solutions

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Fill Table

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

MVP
MVP

Re: Fill Table

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;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Fill Table

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

MVP
MVP

Re: Fill Table

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;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
insurfacto
New Contributor

Re: Fill Table

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