topic Re: Data Table in QlikView

Data Table

upaliwije — Fri, 25 Sep 2015 11:06:19 GMT

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

Re: Data Table

MK_QSL — Fri, 25 Sep 2015 11:14:53 GMT

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;

Re: Data Table

simenkg — Fri, 25 Sep 2015 11:14:58 GMT

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;