Incremental load of column

[bellesol]

Hi,

I'm trying to load a table from Db in an incremental load of the last 4 months,

but the problem is that the month's display as columns and not rows, meaning every load I want to update only the relevant column and rows.

for example - today is 1.2020, the last four-month are from October 2019 till today, so the columns and rows that need to be updated is 

"01sum" only rows of 2020

"10sum" only rows of 2019

"11sum" only rows of 2019

"12sum"  only rows of 2019

Is there a way to do this kind of incremental load?

*I attached an excel file with dummy data.

Thanks

[marcus_sommer]

Your source is a crosstable. Most often it's the best to transform it with The-Crosstable-Load. If your source is really huge and that's the reason why you want to load data incrementally the crosstable-load should be rather the second step and you need to create the fieldlist with some logic within a variable, maybe like this one:

t:
load rowno() as RowNo, F1, F1 & chr(44) & peek('F2') as F2;
load chr(34) & date(addmonths(today(), -recno() + 1), 'MM-YYYY') & chr(34) as F1 autogenerate 4;

let vFieldList = peek('F2', -1, 't');
drop tables t;

and then your load from the db:

x: load $(vFieldList) ID_x, further fields ... from db;

- Marcus

[bellesol]

Hi Marcus,

Thanks for your response, I try to load the data according to your instructions but  I ran into some difficulties.

for example, the outcome of the variable is - '10-2019','11-2019','12-2019','01-2020', (I used char(39) instead of char(34))

and when I added the variable to the load from the DB I couldn't take off the last comma (but I ignored the scrip error and it work )

and then I try to use the cross table load, but I didn't know how to write it correctly, is it possible that you can help me more with writing the script?

[marcus_sommer]

You may adjust it to this:

t:
load *, left(F2, len(F2) - 1) as F3;
load rowno() as RowNo, F1, F1 & chr(44) & peek('F2') as F2;
load chr(39) & date(addmonths(today(), -recno() + 1), 'MM-YYYY') & chr(39) as F1 autogenerate 4;

let vFieldList = peek('F3', -1, 't');
drop tables t;

x: select ID, Description, Category, $(vFieldList) fields ... from db;

y: crosstable(YearMonth, Values, 3) load * resident x;

drop tables x;

- Marcus

[bellesol]

I'm sorry but I'm not sure I know what you mean. ... if you can elaborate more I will appreciate that.

 the direction is exactly what I need, but it's hard for me to implement your solution on mine.

[marcus_sommer]

For me it's not quite clear which part of my suggestion is too tricky to apply - the logic of the creation of the field-list, the values and/or the format of this list, the use of it within the sql or then the following crosstable-transformation?

Quite often it's useful not to apply unknown features / logic on the origin data else to create some dummy-data within an Excel - just a few columns and rows are enough with preferably simple data (not including spaces, any kind of special chars and so on) to concentrate on the logic and not to struggle with additionally formattings / efforts to the syntax.

Such load is very fast and you could adjust both Qlik and Excel quickly and trying it multiple time in a quite short time. If you think you get it you could step by step increase the complexity - maybe by a bit copy & paste from your db and if it's worked like expected it shold be also working if you transfers it into the origin load-part.

- Marcus