manually adding rows to a table that is already loaded

[#1]

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?

[swuehl]

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?

[swuehl · Accepted solution]

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;

[#4]

Wow, that's so effective and so simple!

Thank you!

[yakir_manor]

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 =;