Calculation in Qlikscript

[bhavvibudagam]

Hi Every one,

Please help me on below requirement at Script level.

I have loaded the below source data in Qlikview.

LOAD

    ID,

    name, 

    "KPI name",

    Target,

    Actual,

From path...

I need to calculate the Target Revenue, Actual Revenue,Target Profit,Actual Profit as separate fields in the script .

for example Sum({<KPIname={'Revenue'}>}Target) as Target Revenue in the Script.

Thanks in Advance

[Anil_Babu_Samineni]

may be this?

Sample:

LOAD

    ID,

    name,

    "KPI name",

    Target,

    Actual

From path...

Concatenate (Sample)

Load ID,

    name,

    "KPI name",

    Sum(Target) as [Target Revenue],

    Actual,

Resident Sample Where "KPI name" = 'Revenue' Group By ID, name, "KPI name";

[MK9885]

Hi Anil,

I have a question....

I'm doing Count(Distinct(ID))

Count(Distinct(EMPID))

Count(Distinct(ProductID)) and more in front end....

Can I do this in back end by grouping?

Each expression is being used in different chart. some of the fields are are coming from same table like ID and EMPID in same table, ProductID from different table.

the reason I want to do this is I want to avoid this in front end.. as I'm loading 500 M rows and counting it in front end would make it really slow.

any suggestions?

[devarasu07]

Hi,

Always try to share source as text/excel that would save our time to help u.

try like below

Data:

LOAD * INLINE [

    ID, name, KPI Name, Target, Actual

    3301, abc, Revenue, 100000, 800000

    3302, def, Profit, 30000, 25000

    3303, abc, cost, 20000, 22000

    3306, def, Revenue, 100000, 800000

    3307, abc, Profit, 300000, 250000

    3308, def, cost, 200000, 220000

    3311, abc, Revenue, 5500000, 6000000

    3312, def, Profit, 1500000, 1300000

    3313, abc, cost, 1000000, 1200000

];

RevenueAggr:

load ID,sum(Target) as [Target Revenue], sum(Actual) as [Actual Revenue] Resident Data where [KPI Name]='Revenue' group by ID,name;

ActualAggr:

load ID,sum(Target) as [Target Profit], sum(Actual) as [Actual Profit] Resident Data where [KPI Name]='Profit' group by ID,name;

[devarasu07]

Hi,

Yes u do it at back end level using joins/Concatenate method.

[MK9885]

Can you please give me an example?

Counting 2 fields from same table using joins?

[Anil_Babu_Samineni]

Yes, using

Load DISTINCT Field, Count(Measure) as Measure Group By Field,...;

Note - If we are using Group By that means Unique keys can remove and no need to use DISTINCT Keyword for that