ranking

[#1]

Hi Experts,

I have the below field in a table it’s a combination of  3 fields

Id | monthly | date.

or 

id | weekly | date

I want to rank the date for Id | monthly | date. Separately as a new field called Month Rank

I want to rank the date for Id | weekly | date Separately as a new field called Week Rank

Pleases suggest how to do that

[Anonymous]

Hi,

Then use this

Temp:

LOAD *, if(Peek(ID,-1)<>ID,1,Peek(Flag1,-1)-1) as Flag1

Resident Fact

order by FlagRank, ID, Date desc;

drop table Fact;

Best regards,

Cosmina

[#3]

hi Marco

but it generating synth tables, is there a way to eliminate it

please suggest

[MarcoWedel]

You could recombine the tables created by the generic load like this:

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'Attributes.*') THEN
  LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

(see also: Use cases for Generic Load | Qlikview Cookbook  and Re: Converting rows in table into columns challenge)

But then again, maybe this legendary thread and blog post help as well to answer the question if this kind of synthetic keys should be avoided at all:

Should We Stop Worrying and Love the Synthetic Key?

Qlik Design Blog ‌

regards

Marco