Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

manually adding rows to a table that is already loaded

Let's say I have loaded a table with two fields:

     WeekNum, Y_value.

For some unfortunate reason, the table has some missing weeks.

Example:

WeekNum       Y_value

   1                   100

   2                    105

   4                    200

   5                     237

Week 3 is missing.

I want to be able to go in there and manually add a row for week 3.

For the Y_value, I just wanna copy over the data from week 2 (105).

How can I acheive this?

1 Solution

Accepted Solutions
MVP
MVP

If you want to do some kind of automatic filling of gap lines, you could do it like this:

INPUT:

LOAD * INLINE [

WeekNum,       Y_value

   1,             100

   2,             105

   4,             200

   5,             237

];

   

Let vMin = Peek('WeekNum',0);

Let vMax = Peek('WeekNum',-1);

 

Join LOAD $(vMin)+recno()-1 as WeekNum

AutoGenerate $(vMax)-$(vMin)+1;

 

RESULT:

NOCONCATENATE LOAD WeekNum, if(IsNull(Y_value),peek(Y_value),Y_value) as Y_value

Resident INPUT order by WeekNum;

 

drop table INPUT;

View solution in original post

4 Replies
MVP
MVP

If you loaded your table and labelled it sa INPUT, you can concatenate another with the missing values, just use the same field names:

INPUT:

LOAD

WeekNum, Y_value

from Table;

LOAD * INLINE [

WeekNum, Y_value

3, 105

];

You should get one table called INPUT with all values.

Or do you want to create these values kind of automatic?

MVP
MVP

If you want to do some kind of automatic filling of gap lines, you could do it like this:

INPUT:

LOAD * INLINE [

WeekNum,       Y_value

   1,             100

   2,             105

   4,             200

   5,             237

];

   

Let vMin = Peek('WeekNum',0);

Let vMax = Peek('WeekNum',-1);

 

Join LOAD $(vMin)+recno()-1 as WeekNum

AutoGenerate $(vMax)-$(vMin)+1;

 

RESULT:

NOCONCATENATE LOAD WeekNum, if(IsNull(Y_value),peek(Y_value),Y_value) as Y_value

Resident INPUT order by WeekNum;

 

drop table INPUT;

View solution in original post

Not applicable

Wow, that's so effective and so simple!

Thank you!

Contributor III
Contributor III

you can use 'Load Inline', this is good solution if you already know the lines you want to add

i wanted a log table to log my stuff so i did it like so:

     LET vStartTime = Now();

     // Log Table Creation or load

     LET vLogTable_NotExists = IsNull(QvdCreateTime('$(vFilePath).log.csv'));

     IF $(vLogTable_NotExists) = 0 THEN

       ScriptLog: // load the existing table

       LOAD * FROM $(vFilePath).log.csv (txt, utf8, embedded labels, delimiter is ',', msq);

     ELSE

       ScriptLog: // create table if no log table exists

       LOAD

       now() AS TimeStamp,

       'General' AS Type,

       'Create CSV Log' AS Message

       AUTOGENERATE(1);

     ENDIF

     // add row

     LET TimeStamp = now();

     //ScriptLog:

     LOAD * INLINE [

     TimeStamp,Type,Message

     $(TimeStamp),General,Reload Start

     ];

     // add two rows

     LET vEndTime = Now();

     LET vDuration = Interval(Timestamp#('$(vEndTime)') - Timestamp#('$(vStartTime)'), 'hh:mm:ss');

     LET TimeStamp = now();

     LOAD * INLINE [

     TimeStamp,Type,Message

     $(TimeStamp),Run Elapsed,$(vDuration)

     $(TimeStamp),General,Reload End

     ];

     // store the table in csv

     STORE ScriptLog INTO $(vFilePath).log.csv (txt);

     // dump variables so they dont appear on doc variables

     LET vFilePath =;

     LET vStartTime =;

     LET vEndTime =;

     LET vDuration =;