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

Group by not working for Fact table

Hi,

I am getting an error field not found- End User Key     while applying Group by.

Note : I already have that column in Fact table (Fact) coming from tmp1_trans table.

Code :

_____________________________________________________________
Fact:
LOAD *,
If([Invoiced Date]>=SDB1 and [Invoiced Date]<=EDB1,1,0) as Flag1,
If([Invoiced Date]>=SDB2 and [Invoiced Date]<=EDB2,1,0) as Flag2,
If([Invoiced Date]>=SDB3 and [Invoiced Date]<=EDB3,1,0) as Flag3

resident tmp1_trans;

 

CustTemp:

  load [End User Key] ,

  sum(Flag1) as F1,

  Sum(Flag2) as F2,

  Sum(Flag3) as F3

  resident Fact

Group by [End User Key]

______________________________________________________________

Am i using correct group by syntax for qlikview.Could you please help.

Thanks in advance.

Tags (2)
6 Replies
MVP & Luminary
MVP & Luminary

Re: Group by not working for Fact table

Hi,

Can you attach the full script, I think [End User Key] is not present in the tmp1_trans table.

Regards,

jagan.

Not applicable

Re: Group by not working for Fact table

Hi Jagan,

End User Key is present in tmp1_trans.

Not applicable

Re: Group by not working for Fact table

Simple END USER KEY without group by clause is working fine for same table.

example:

CustTemp:

  load [End User Key] ,

  Flag1 as F1,

  Flag2 as F2,

  Flag3 as F3

  resident Fact;

But when i am pulling the same column (End user key) in group by,Its giving an error : Field not found.

I think there is some syntax issue with group by.

MVP & Luminary
MVP & Luminary

Re: Group by not working for Fact table

Hi,

Qlikview is case sensitive.  Check whether the case you are using is correct.

[End User Key]

Can you attach script for tmp1_trans table.

Regards,

Jagan.

Not applicable

Re: Group by not working for Fact table

Thanks jagan for quick reply.

I am copying the same field used in tmp1_Transactions.So case mismatch issue is not there .

I cant attach whole script but i can show you sample of tmp1_Transactions:

tmp1_Transactions:


LOAD
 

text([Article No.]) as %KEY_ITEM,

if([Invoice Date]='1900-01-01','',[Invoiced Date]) as [Invoiced Date],


if(OrderIntake=0,
if(openorder=1,'Open','Invoiced')) as [Order status],

num(OrderCompany)&'|'& text(applymap('MAP_ITEM_PG',text([Article No.]),0)) as KEY_SCOPE_DISCOUNT,

Applymap('MAP_PARTTYPE_CURR',text([Article No.]),0) as Local_Curr,


Applymap('MAK',[Order No.],'N/A')  as [End User Key],

text([Article No.])  as KeyScope,


Resident tmpClosedOpenOrders

MVP & Luminary
MVP & Luminary

Re: Group by not working for Fact table

Hi,

Did you used any Qualify statement in your script?  Check the column name in Table viewer whether it is [End User Key] or any TableName.[End User Key]? If it is TableName.[End User Key] then use this as column name.

Hope this helps you.

Regards,

jagan.

Community Browser