QlikView App Dev

[djbloiss]

I am new to qlikview and going through videos to learn.  I am currently on the ETL section of the video but I want to improve upon what teacher is showing (I think I learn better this way).  What we are currently doing is creating an inline load of a table to log the table.  How he had us do it is we copied the code, almost identical, after each table to log that table.  I wanted to change that in to a sub and then just pass the table name in.  I have been able to figure it out 90% of the way but the last little bit is giving me an error. Here is the code:

Sub LogTable( vTableName )

    zTemp:

    LOAD Max(RowNum) as MaxRowNum

    Resident [$(vTableName)];

    let vDateLoaded = Today();

    let vSourceFile  = 'eCommerce.' & '$(vTableName)';

    let vSourceFileType = 'SQL';

    let vRowCount = Peek('MaxRowNum', 0, 'zTemp');

    DROP table zTemp;

    DROP Field RowNum from [$(vTableName)];

    QVDLoadLog:

    LOAD * INLINE [

        SourceFile, SourceFileType, DateLoaded, RowCount

        '$(vSourceFile)', '$(vSourceFileType)', '$(vDateLoaded)', '$(vRowCount)'

    ];

End sub

OnlineSales:

Load *, RowNo() as RowNum;

SQL SELECT CurrencyKey,

    CustomerKey,

    DateKey,

    DueDate,

    ExtendedAmount,

    Freight,

    OrderDate,

    OrderQuantity,

    ProductKey,

    ProductStandardCost,

    PromotionKey,

    RevisionNumber,

    SalesAmount,

    SalesOrderLineNumber,

    SalesOrderNumber,

    SaleTypeKey,

    ShipDate,

    TaxAmt,

    TotalProductCost,

    UnitPrice,

    UnitPriceDiscountPct

FROM eCommerce.dbo.OnlineSalesByDate;

CALL LogTable('OnlineSales')

But it keeps telling me it cannot find the table (with no name), so I gather I am passing the variable incorrectly.  What am I doing wrong?

[rubenmarin]

Hi Daniel, parameters are not retrieved with $( as variables, you can try this few changes:

Sub LogTable(TableName)

   LET vTableName=TableName;

    zTemp:
    LOAD Max(RowNum) as MaxRowNum
...

[marcus_sommer]

You don't need in most of the cases to transfer the table-name (an exception are mapping-tables) or to reload the origin table to get the number of records and similar stuff - most of them could you generate directly with the field-and table-functions, for example: tablename(nooftables() - 1) for the last loaded table and noofrows(tablename(nooftables() - 1)) to get the number of records.

Here you will find many more input and ideas: Re: How get RowNo() inside a module macro

- Marcus