topic Re: load script sum lines while loading to one line in QlikView

load script sum lines while loading to one line

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I hope someone of you have a good idea how to handle a load issue.

I have a loadscript where I want to load a table. Without where-clause I have over 8 Million lines but with Where-Clause the summarized amounts are wrong.

How can I say the following?

Load all lines where "Posting Date" < '01.01.2017' as one line per "G_L Account No_" with description Starting "Balance 01.01.2017" and

summarize the field "Amount" and fill in all other fields a 0?

Lines "Posting Date" >= '01.01.2017' can be loaded line by line.

LOAD

timestamp,

"Entry No_",

"G_L Account No_",

"Posting Date" as PostingDate,

"Document Type",

"Document No_",

Description,

"Bal_ Account No_",

num(Amount,'#.##0,00') as Amount,

"Global Dimension 1 Code",

"Global Dimension 2 Code",

"User ID",

"Source Code",

"System-Created Entry",

"Prior-Year Entry",

"Job No_",

Quantity,

"VAT Amount",

"Business Unit Code",

"Journal Batch Name",

"Reason Code",

"Gen_ Posting Type",

"Gen_ Bus_ Posting Group",

"Gen_ Prod_ Posting Group",

"Bal_ Account Type",

"Transaction No_",

num("Debit Amount",'#.##0,00') as "Debit Amount",

num("Credit Amount",'#.##0,00') as "Credit Amount",

"Document Date",

"External Document No_",

"Source Type",

"Source No_",

"No_ Series",

"Tax Area Code",

"Tax Liable",

"Tax Group Code",

"Use Tax",

"VAT Bus_ Posting Group",

"VAT Prod_ Posting Group",

"Additional-Currency Amount",

"Add_-Currency Debit Amount",

"Add_-Currency Credit Amount",

"Close Income Statement Dim_ ID",

"IC Partner Code",

Reversed,

"Reversed by Entry No_",

"Reversed Entry No_",

"Dimension Set ID",

"Prod_ Order No_",

"FA Entry Type",

"FA Entry No_",

"Pmt_ Import Entry No_",

"Orig_ Currency Code",

"Original Amount (FCY)",

"Posted at",

"Applying Entry",

"Amount to Apply",

"Applies-to Doc_ Type",

"Applies-to Doc_ No_",

"Open",

"Closed by Entry No_",

"Closed at Date",

"Closed by Amount",

"Applies-to ID",

Positive,

"Invoice Entrance Date";

SQL SELECT *

FROM "MyDB_Live".dbo."Company AG$G_L Entry"

//where "Posting Date" >= '01.01.2017'

//AND "Posting Date" <= '01.01.2019'

Very much thanks in advance

Chris

Re: load script sum lines while loading to one line

olivierrobin — Fri, 27 Apr 2018 08:59:10 GMT

hello

if you want to sum a field use sum() and have a group by clause

(whether in your sql to minimize data or in qlik )

Re: load script sum lines while loading to one line

Anonymous — Fri, 27 Apr 2018 09:07:36 GMT

Hello Olivier,

yes I want to minimize the data loaded in qlikview. the values from 1998-2016 are important for correct sums and balances but I only need the details for datalines > 31.12.2016.

It is loading nearly 8 Million lines or loading only 1.2 Million lines.

Hope this helps.

Re: load script sum lines while loading to one line

olivierrobin — Fri, 27 Apr 2018 09:12:44 GMT

if I understand, you want :

the sum of rows beginning in 1998

BUT the detail only from 2016 ?

So, in this case, I would change the SQL request to :

retrieve one row with the sum

retrieve one row per detail level

if you can join them in the query, it is better for performance

if not, you'all have to joib them in Qlik

Re: load script sum lines while loading to one line

Anonymous — Fri, 27 Apr 2018 09:14:56 GMT

i want to have one row per GL-Account from 1998-2016 and beginning from 2017 every line.

The sum is not the sum of rows but the sum of field amount

But unfortunately I have no idea how to do this codewise.

Re: load script sum lines while loading to one line

olivierrobin — Fri, 27 Apr 2018 09:19:50 GMT

in pseudo sql and with approximative syntax that would give

select keys, sum(Amount) as Amount,

all other colums as 0

from Table

where date>1998

group by keys

union

select keys, amount, .......

from Table

where date>2017

Re: load script sum lines while loading to one line

sasiparupudi1 — Fri, 27 Apr 2018 13:07:57 GMT

After your load statement, may be use an inner join

Inner Join(Your Table)

Load

G_L Account No_,

Max("Posting Date") as "Posting Date",

SUM(Amount) as TotalGLAmount

Resident [Your Table]

Where

"Posting Date" < makedate(1,1,2017)

And WildMatch(description,'Balance 01.01.2017')>0

Group By

G_L Account No_

;