topic manually adding rows to a table that is already loaded in QlikView

manually adding rows to a table that is already loaded

Wed, 01 Feb 2012 21:58:14 GMT

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?

manually adding rows to a table that is already loaded

swuehl — Wed, 01 Feb 2012 22:22:56 GMT

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?

manually adding rows to a table that is already loaded

swuehl — Wed, 01 Feb 2012 22:30:21 GMT

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;

manually adding rows to a table that is already loaded

Thu, 02 Feb 2012 19:00:04 GMT

Wow, that's so effective and so simple!

Thank you!

Re: manually adding rows to a table that is already loaded

yakir_manor — Sun, 18 Oct 2015 16:53:33 GMT

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