Script: how to calculate number of rows in a table

[#1]

In script, I create a table from an existing created table in the same script. The existimg table contains invoice numbers and various facts pertaining to these invoices (let's call it InvoiceTable). From this table, I want to create a new table (let's call this NewTable) which

• Contains each invoice only once, which I do through LOAD DISTINCT InvoiceNumber RESIDENT InvoiceTable
• And a field which contains the cost for each invoice, which is the total coat divided by the number of invoices.
  My question is: how can I determine the total number of distinct invoices and use this number in an expreesion to calculate this second field?

Thanks a lot for helping me out!

[jvitantonio]

Here it is

[#3]

That's a quick reply. Thanks!

But... this is unfortunately not what I am looking for. I need to count the total number of distinct invoices. Not how ofetn each invoice occurs. In your example, the number of distinct invoices is 2. Any suggestions?

MarcD

[swuehl · Accepted solution]

I understood your request somekind different from qlikuser14.

If you want to get the total number of distinct invoices, you can use fieldvaluecount() for that.

So maybe like this:

Tab1:

LOAD * INLINE [

ID

1

2

2

3

3

3

];

Let TotalCost = 10000;

Tab2:

LOAD distinct ID, $(TotalCost)/ FieldValueCount('ID') as CostPerInvoice

resident Tab1;

This will of course return the same value for CostPerInvoice for all lines, so I am not sure if this is what you asked for.

Regards,

Stefan

[jagan]

Hi,

Please check the script below.

Directory;

a:

LOAD * INLINE [

    Invoice, Value

    1, 30

    1, 90

    2, 10

    2,10

    2,10

];

Temp:

LOAD

    DISTINCT Invoice AS Inv

RESIDENT a;

LET vDistInvoice = FieldValueCount('Inv') ;

DROP Table Temp;

b:

load

Invoice,

sum(Value)/$(vDistInvoice) as Sum,

count( DISTINCT Invoice) AS InvCount

Resident a

group by Invoice;

drop table a;

Regards,

Jagan.

[jvitantonio]

Ok,here it is

[#7]

Hi All,

Thanks for helping me out.

Regards,

MarcD.