Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Adding formula created after Load statement to this statement

Hi,

I have a simple problem, but I am struggling with it already 2 hours with poor result.

I have the following statement:

LOAD [Group acct],

[Group Acc Description],

Account,

[G/L acct long text],

BusA,

[Profit Ctr],

Tr.Prt,

Dv,

Market,

[Total HC] as Amount_Total,

External as Amount_Ext,

Internal as Amount_Int,

subfield(FileBasename(),'_',1) as Month,

subfield(FileBasename(),'_',2) as Country

FROM

[Basic_Data\*.xlsx]

(ooxml, embedded labels, table is Sheet1);

I would like to redefine Amount_Total variable in the following way:

If (Country = 'PL' , [Total HC] / 4 , [Total HC]) as Amount_Total

and in similar way the wariables Amount_Ext and Amount_Int

Could you please help me doing this?

2 Replies
swuehl
Not applicable

Re: Adding formula created after Load statement to this statement

You could either do

LOAD *,

If (Country = 'PL' , [Total HC] / 4 , [Total HC]) as Amount_Total;

LOAD [Group acct],

[Group Acc Description],

Account,

[G/L acct long text],

BusA,

[Profit Ctr],

Tr.Prt,

Dv,

Market,

[Total HC],                //remove the rename here

External as Amount_Ext,

Internal as Amount_Int,

subfield(FileBasename(),'_',1) as Month,

subfield(FileBasename(),'_',2) as Country

FROM

[Basic_Data\*.xlsx]

(ooxml, embedded labels, table is Sheet1);

or

LOAD [Group acct],

[Group Acc Description],

Account,

[G/L acct long text],

BusA,

[Profit Ctr],

Tr.Prt,

Dv,

Market,

If (subfield(FileBasename(),'_',2) = 'PL' , [Total HC] / 4 , [Total HC]) as Amount_Total

External as Amount_Ext,

Internal as Amount_Int,

subfield(FileBasename(),'_',1) as Month,

subfield(FileBasename(),'_',2) as Country

FROM

[Basic_Data\*.xlsx]

(ooxml, embedded labels, table is Sheet1);

in the first example you could drop field [Total HC] if you want.

Not applicable

Re: Adding formula created after Load statement to this statement

Hi

thanks.. of course, now I see my mistake. Instead of using the formula in "if" statememnt I was using the name created in the same statement...

Thanks a lot for this hint

//Draszor