Add a dimension value in load scrip

[whizover]

Hi Guys,

I'm new to QLIK Sense, could you kindly tell me if the following action can be achieved?

The original data looks like:

load * Inline

[

Type,Value

A, 100

B, 200

]

I would like to add a Type C, and the value is the calculated result of Type A - Type B.

It will be looked like:

Type,Value

A, 100

B, 200

C, -100

Many Thanks!

Edward

[swuehl · Accepted solution]

Maybe like

TABLE:

load * Inline

[

Type,Value

A, 100

B, 200

];

LOAD 'C' as Type,

Rangesum(Sum(If(Type='A',Value)),-Sum(If(Type='B',Value))) as Value

Resident TABLE;

[whizover]

Hi Stefan,

Thanks a lot! It works with the single table calculation!

However,  can I follow up with another question? I believe it is related to where clause and data comparison

When calculating multiple data with load, the sum value is duplicated as below:

(My file are .dat files.)

//////////////////////////////////////////////////////////////////////////////////////////////////

SUB DoDir (Root)

FOR each File in filelist(Root& '/*.dat')

let vMR_NO_Abbr = Purgechar(SubField('$(File)','/',-1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ/.');
let vMR_Date= Date(Date#(right('$(Root)',6),'YYYYMM'),'YYYYMM');

///// Root looks like this:  \Performance Review\EIMR\201805

///// Different month file are saved in different month folder such as \Performance Review\EIMR\201806

[Table]:

Load
Date(Date#(right(RootName,6),'YYYYMM'),'YYYYMM') as MR.DataDate,
Purgechar(FileName_Branch,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as Key.NO_Abbr,
[@1] as [Function_Element],
[@128] as [Total_Modernization],
[@158] as [Service_Contra],
[@161] as [Repairs],
[@164] as [Spare_Parts];
LOAD
'$(Root)' as RootName,
FileBaseName() as FileName_Branch,
[@1],
[@128],
[@158],
[@161],
[@164]
FROM [$(File)]
(txt, codepage is 936, no labels, delimiter is '\t', msq, header is 3 lines)
Where len([@1])>0 and [@1] <> 'Total' and [@1] <> 'Lead column'
;

Concatenate load
[MR.DataDate]
,[Key.NO_Abbr]
,'Total Struco' as [Function_Element]
,Rangesum(If([Function_Element]='Total Indirect cost',[Total_Modernization]),If([Function_Element]='Total PL Cost',[Total_Modernization])) as [Total_Modernization]

resident [Table]
where Key.NO_Abbr= '$(vMR_NO_Abbr)'
;

NEXT File

FOR each Dir in dirlist (Root&'/*')

CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('lib://EI_MR')

//////////////////////////////////////////////////////////////////////////////////////////////////

All other results are fine except the value of the calculated 'Total Struco' duplicates when I load multiple month.

I believe it is because the Concatenate load loads the previous data in Table every time in the loop.

So I tried to wrote a where clause:

Key.NO_Abbr= '$(vMR_NO_Abbr)' and [MR.DataDate] = '$(vMR_Date)'

Trying to limit the Concatenate Load.

However, if I keep the [MR.DataDate] = '$(vMR_Date)' in the script, the Concatenate Load returns no value (0 rows).

So I tried to change it to [MR.DataDate] <= '$(vMR_Date)', so as to check my data format. The value are showed but still duplicated (as expected).

Could you kindly check where I got wrong?

REALLY Thanks!

Kind Regards,

Edward

[swuehl]

I can't see a table source for your second load, are you sure you've posted the real code?

(table source would be given by e.g. FROM Table or RESIDENT Table)

Next, you've used Rangesum() instead of an aggregation function like Sum(), so you won't get the records aggregated, but keep all recods.

[whizover]

Thanks Stefan,

Yep, you are right, I missed the resident Table when I copied into the board.

It should be like:

Concatenate load
[MR.DataDate]
,[Key.NO_Abbr]   
,'Total Struco' as [Function_Element]
,Rangesum(If([Function_Element]='Total Indirect cost',[Total_Modernization]),If([Function_Element]='Total PL Cost',[Total_Modernization])) as [Total_Modernization]

resident [Table]
where Key.NO_Abbr= '$(vMR_NO_Abbr)'

The problem I've got now is that if I put [MR.DataDate] = '$(vMR_Date)' into the where clause, the [MR.DataDate] = '$(vMR_Date)' does not work...

[swuehl]

Maybe try comparing the date numerical values:

let vMR_Date_num = Num(Date#(right('$(Root)',6),'YYYYMM'));

....

where Key.NO_Abbr= '$(vMR_NO_Abbr)' and [MR.DataDate] = $(vMR_Date_num)

;

And again, if you want an aggregated table, use an aggregation function like Sum() instead of Rangesum().

[whizover]

Cool, got it.

Really thanks a lot for your help!