Data Table

[upaliwije]

Hi,

I have following set of data in a QVD file

REF_NO	TYPE	AMOUNT
AA0015D0069191	3	970.5
AA0015D0069251	5	25.13
AA0015D0069251	3	2.03
AA0015D0069306	5	1661.21
AA0015D0069306	3	399.74
AA0015D0069307	5	408.08
AA0015D0069307	3	102.02
AA0015D0069314	5	410.35
AA0015D0069314	3	91.67
AA0015D0069334	5	746.63
AA0015D0069334	3	167
AA0015D0069376	5	701.91
AA0015D0069376	3	56.52
AA0015D0069383	5	2952.01
AA0015D0069383	3	248.6
AA0015D0069393	5	522.85
AA0015D0069393	3	116.87
AA0015D0069403	5	746.63
AA0015D0069403	3	167
AA0015D0069511	3	114.08
AA0015D0069511	3	510.38
AA0015R0217932	3	50
AA0015R0217969	3	50
AA0015R0218020	3	50
AA0015R0218413	3	50

I want to do add following fields to this table and reload the data file

If there is single record for same ref_no  it should be marked as Y otherwise N in a  Field 1

When multiple records exits for a single ref_no max amount should be marked as MAX in field 2

Pls  help me to write the script for addition of above fields to my QVD table

[MK_QSL · Accepted solution]

Data:
Load * Inline
[
  REF_NO, TYPE, AMOUNT
  AA0015D0069191, 3, 970.5
  AA0015D0069251, 5, 25.13
  AA0015D0069251, 3, 2.03
  AA0015D0069306, 5, 1661.21
  AA0015D0069306, 3, 399.74
  AA0015D0069307, 5, 408.08
  AA0015D0069307, 3, 102.02
  AA0015D0069314, 5, 410.35
  AA0015D0069314, 3, 91.67
  AA0015D0069334, 5, 746.63
  AA0015D0069334, 3, 167
  AA0015D0069376, 5, 701.91
  AA0015D0069376, 3, 56.52
  AA0015D0069383, 5, 2952.01
  AA0015D0069383, 3, 248.6
  AA0015D0069393, 5, 522.85
  AA0015D0069393, 3, 116.87
  AA0015D0069403, 5, 746.63
  AA0015D0069403, 3, 167
  AA0015D0069511, 3, 114.08
  AA0015D0069511, 3, 510.38
  AA0015R0217932, 3, 50
  AA0015R0217969, 3, 50
  AA0015R0218020, 3, 50
  AA0015R0218413, 3, 50
];


Left Join (Data)
Load 
  REF_NO, 
  IF(COUNT(REF_NO)>1,'N','Y') as Flag_Appereance,
  IF(COUNT(REF_NO)>1, Max(AMOUNT)) as MaxAmount 
Resident Data
Group By REF_NO;


NoConcatenate
Final:
Load
  REF_NO, 
  TYPE,
  AMOUNT,
  Flag_Appereance,
  IF(AMOUNT = MaxAmount,'Max') as Flag_Max
Resident Data;


Drop Table Data;

[simenkg]

Fact_t1:
Load REF_NO, TYPE, AMOUNT from file.qvd (qvd)

left join(Fact_t1)

load REF_NO, Count(REF_NO) as RefCount, max(AMOUNT) as maxAmount

resident Fact_t1

group by REF_NO;

noconcatenate

Fact:

load REF_NO, TYPE,AMOUNT,if(RefCount = 1, 'Y','N') as [Field 1],

if(RefCount > 1 and AMOUNT = maxAmount, 'MAX') as [Field 2]

resident Fact_t1;

drop table Fact_t1;