Qlikview - Insert a recno() for each change in a column

Question

I have a table with Document Numbers and items. We have integrated our software to a third party accounting package. The import to the third party does not take our document line numbers, however, it posts into their backend in the same order.

For Example:

My Software:

The import to the Third Party Software (only takes limited fields as they do not manage stock/batch controlled stock):

My goal is to create a new column in the second table to add row numbers for each change in Document numbers. This will allow me to create a unique key that is what I need to link the two databases' tables together.

Answer 1

If I understand the issue correctly, what you want to do is to start with [Row. No.] 1 for every new value in [Doc. No.], and increment the field [Row. No.] by 1 as long as [Doc. No.] is the same as the previous row. One way to accomplish that could be the following:

//load the table, in this example it is an inline table 
//but you would load it from another source
    table2:
    load * inline [
    Doc. No.,Description,Qty,Batch,Value
    Doc 1, Item1, 1, 10
    Doc 1, Item1, 2, 10
    Doc 1, Item1, 3, 10
    Doc 2, Item2, 1, 20
    Doc 3, Item3, 1, 30
    Doc 3, Item3, 1, 30
    Doc 3, Item3, 1, 30
    ];

//define an empty table that into which values can be "concatenateloaded"
    newTable2:
    load * inline [
    dummy
    1
    ];


//define starting values to be used in the loop
    let rownodigit = 0;
    let lastdocno = '';


//Iterate over each row in the table, and use the values to build 
//the new table row for row. If the table is large this would 
//require a significant amount of time...

FOR rowno = 0 TO noOfRows('table2')-1
    let docno = peek('Doc. No.', rowno, 'table2');
    let desc = peek('Description', rowno, 'table2');
    let qty = peek('Qty', rowno, 'table2');
    let batch = peek('Batch', rowno, 'table2');
    let value = peek('Value', rowno, 'table2');

    //determine what value the [Row. No.] field is to be given
    if docno=lastdocno then
        rownodigit = rownodigit + 1;
    else
        rownodigit = 1
    endif

    //build the table by generating a new row into the new table
    concatenate (newTable2) 
    load 
        '$(docno)' as [Doc. No.],
        '$(desc)' as [Description],
        $(qty) as [Qty],
        $(batch) as [Batch],
        $(value) as [Value],
        $(rownodigit) as [Row. No.]
    autogenerate (1)
    ;

    let lastdocno = docno; //store the value of docno into a new variable
                           //for comparison in the top of the loop in 
                           //the next iteration
NEXT rowno

drop field dummy; //this field was only needed to create the temporary table
drop table table2; //drop the orgiginal table, otherwise we would have 
                   //a lot of synthetic keys

//now fix the table and create the [Key] field
    table2:
    load *, [Doc. No.]&[Row. No.]&[Description] as Key
    resident newTable2
    where len([Doc. No.])>0 //this avoids the blank row generated 
                            //by the first dummy insert
    ;

//this was a temporary table that we generated and it should now be dropped
drop table newTable2; 

Answer 2

IF ANYBODY NEEDS TO SOLVE THIS PROBLEM IN SQL-SIDE :

Just add into your sql statement

SELECT ROW_NUMBER() OVER(PARTITION BY [Doc No.] ORDER BY {Anything you want/need} ASC) AS RowNumber,....,....

Detailed SQL Description is in the below link. (From Microsoft Site, Can i share i dont know, if no sorry Admin) Microsoft docs link