topic Re: group by Field Values in QlikView

group by Field Values

Wed, 29 Apr 2015 11:41:39 GMT

HI Experts

I have a field Product_name and the field Values are Below

I want to classify the values in to two groups, the field value in blue color into ProductA and rest into ProductB, and I want to create a listbox with ProductA and ProductB, and if Makr selection in that listbox my app should show the changes according to it, please Help

251
252
256
263
355
356
400
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
AMP
ECP
FMT
KAR
LAS
MIL
SEA
SIP
SYK
243
244
245
248
255
265
527
528
529
530
531
532
533
534
535
536
537
538
546
570
571
572
940
965

Re: group by Field Values

arulsettu — Wed, 29 Apr 2015 11:49:20 GMT

try this

KeepChar(Product_name ,'0123456789') as Product_a,

PurgeChar(Product_name ,'0123456789') as Product_b

Re: group by Field Values

anbu1984 — Wed, 29 Apr 2015 11:59:32 GMT

Do you have any logic to split into ProductA and ProductB?

If it is hard-coded values, then you can create Inline table like below

Load * Inline [

Product_Name,ProductGroup

251,ProductA

252,ProductA

...

587,ProductA

588,ProductB

...

965,ProductB ];

Re: group by Field Values

Anonymous — Wed, 29 Apr 2015 12:00:36 GMT

Hi John

I guess based on colour you may not able restrict in the script. Kindly let us know if there is any other logic or what you want to do?

-Jay

Re: group by Field Values

Gysbert_Wassenaar — Wed, 29 Apr 2015 12:01:18 GMT

LOAD * INLINE [

Product_name, Group

251,ProductA

252,ProductA

256,ProductA

263,ProductA

355,ProductA

356,ProductA

400,ProductA

575,ProductA

576,ProductA

577,ProductA

578,ProductA

579,ProductA

580,ProductA

581,ProductA

582,ProductA

583,ProductA

584,ProductA

585,ProductA

586,ProductA

587,ProductA

588,ProductB

589,ProductB

590,ProductB

591,ProductB

592,ProductB

593,ProductB

594,ProductB

595,ProductB

596,ProductB

597,ProductB

598,ProductB

599,ProductB

AMP,ProductB

ECP,ProductB

FMT,ProductB

KAR,ProductB

LAS,ProductB

MIL,ProductB

SEA,ProductB

SIP,ProductB

SYK,ProductB

243,ProductB

244,ProductB

245,ProductB

248,ProductB

255,ProductB

265,ProductB

527,ProductB

528,ProductB

529,ProductB

530,ProductB

531,ProductB

532,ProductB

533,ProductB

534,ProductB

535,ProductB

536,ProductB

537,ProductB

538,ProductB

546,ProductB

570,ProductB

571,ProductB

572,ProductB

940,ProductB

965,ProductB

];

Re: group by Field Values

Wed, 29 Apr 2015 12:17:47 GMT

HI JOHN

NOT based on colors but need to differentiate that so changed colors, but all belongs to one field Product_Name

Re: group by Field Values

Wed, 29 Apr 2015 12:18:46 GMT

gwassenaar

Anbu cheliyan

I am trying this way

Re: group by Field Values

Anonymous — Wed, 29 Apr 2015 12:19:49 GMT

HI John

it will be difficult to identify if you don't have any flags/conditions. Other may give right ans

-Jay

Re: group by Field Values

sunny_talwar — Wed, 29 Apr 2015 12:22:09 GMT

Try the following script:

Temp:

LOAD * INLINE [

Product_Name

AMP

TFD

FMT

KAR

LAS

MIL

SEA

SIP

SYK

527

528

529

530

531

532

533

534

535

536

537

538

546

570

571

572

573

940

965

243

244

245

248

251

252

255

256

263

265

355

356

400

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

];

Table1:

LOAD Product_Name as ProductA

Resident Temp

Where IsText(Product_Name) or Match(Product_Name, 527,528,529,530,531,532,533,534,535,536,537,538,546,570,571);

Table2:

LOAD Product_Name as ProductB

Resident Temp

Where not Exists(ProductA, Product_Name);

DROP Table Temp;

HTH

Best,

Sunny

Re: group by Field Values

Wed, 29 Apr 2015 12:23:33 GMT

HI All

I am Loading this Product_name From a Product Table

I am Using

Product_Name

FROM

[\\TVHC2-QVP01\Data\QVDs\DIMENSIONAL\KPRODUCT.qvd]

(qvd)

where Mixmatch(PC, '243', '244', '245', '248', '251', '252', '255', '256','263','265','355','356','357','400','572',

'575','576','577','578','579','580','581','582','583','584','585','586','587','588','589','590','591','592','593','594',

'595','596','597','598','599','527','528','529','530','531','532','533','534','535','536','537','538','546','570','571',

'572','573','940','965','AMP','ECP','FMT','KAR','LAS','MIL','SEA','SIP','SYK');

is there any Chance I can separate here Itself

Re: group by Field Values

sunny_talwar — Wed, 29 Apr 2015 12:27:21 GMT

May be this way:

If(Match(Product_Name, 'AMP','ECP','FMT','KAR','LAS','MIL','SEA','SIP','SYK', ....) as ProductA,

If(Match(Product_Name, .....) as ProductB

FROM

[\\TVHC2-QVP01\Data\QVDs\DIMENSIONAL\KPRODUCT.qvd]

(qvd)

where Mixmatch(PC, '243', '244', '245', '248', '251', '252', '255', '256','263','265','355','356','357','400','572',

'575','576','577','578','579','580','581','582','583','584','585','586','587','588','589','590','591','592','593','594',

'595','596','597','598','599','527','528','529','530','531','532','533','534','535','536','537','538','546','570','571',

'572','573','940','965','AMP','ECP','FMT','KAR','LAS','MIL','SEA','SIP','SYK');

Re: group by Field Values

anbu1984 — Wed, 29 Apr 2015 12:59:43 GMT

Load

Product_Name,

If(Match(Product_Name,'251','252',....'587'),'ProductA',ProductB') As ProdGroup //Mention all the Product names belong to ProductA in this Match()

FROM

[\\TVHC2-QVP01\Data\QVDs\DIMENSIONAL\KPRODUCT.qvd]

(qvd)

where Mixmatch(PC, '243', '244', '245', '248', '251', '252', '255', '256','263','265','355','356','357','400','572',

'575','576','577','578','579','580','581','582','583','584','585','586','587','588','589','590','591','592','593','594',

'595','596','597','598','599','527','528','529','530','531','532','533','534','535','536','537','538','546','570','571',

'572','573','940','965','AMP','ECP','FMT','KAR','LAS','MIL','SEA','SIP','SYK');

Re: group by Field Values

Wed, 29 Apr 2015 13:01:46 GMT

HI Sunny

i tried your way

it says expression error

Error in Expression:if takes 2-3 parametres

Re: group by Field Values

sunny_talwar — Wed, 29 Apr 2015 13:03:31 GMT

Sorry forgot to close the if statement:

If(Match(Product_Name, 'AMP','ECP','FMT','KAR','LAS','MIL','SEA','SIP','SYK', ....), Product_Name) as ProductA,

If(Match(Product_Name, .....), Product_Name) as ProductB

FROM

[\\TVHC2-QVP01\Data\QVDs\DIMENSIONAL\KPRODUCT.qvd]

(qvd)

where Mixmatch(PC, '243', '244', '245', '248', '251', '252', '255', '256','263','265','355','356','357','400','572',

'575','576','577','578','579','580','581','582','583','584','585','586','587','588','589','590','591','592','593','594',

'595','596','597','598','599','527','528','529','530','531','532','533','534','535','536','537','538','546','570','571',

'572','573','940','965','AMP','ECP','FMT','KAR','LAS','MIL','SEA','SIP','SYK');

Re: group by Field Values

Wed, 29 Apr 2015 13:12:26 GMT

HI Sunny

This is Loading all the Product_name some 800,560 not the particular ones

Re: group by Field Values

Wed, 29 Apr 2015 13:15:26 GMT

Anbu Cheliyan

This Loading the entire Product table but not the expected ones

Re: group by Field Values

Wed, 29 Apr 2015 13:23:51 GMT

I Tried the Way Mentioned By Abucheliyan & Gysbert

It Worked

HI All

Thanks for your Help