topic Adding formula created after Load statement to this statement in QlikView

Adding formula created after Load statement to this statement

Fri, 16 Dec 2011 20:47:42 GMT

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?

Re: Adding formula created after Load statement to this statement

swuehl — Fri, 16 Dec 2011 21:16:37 GMT

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);

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.

Re: Adding formula created after Load statement to this statement

Mon, 19 Dec 2011 07:12:07 GMT

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