topic Re: how to display with pre-fix and with pre-fix values in QlikView

how to display with pre-fix and with pre-fix values

Srinivas — Fri, 29 May 2015 09:33:08 GMT

Hi All,

I want to display as like below screen shot way who have two values like with pre-fix value and without pre-fix value as a another table or qvd.

Am attaching file and data, Could you please help me on this.

Advance Thanks,

Munna

Re: how to display with pre-fix and with pre-fix values

robert_mika — Fri, 29 May 2015 10:03:24 GMT

Not entirely sure but try this:

TextBetween( [Product Number],'-','') as ProductNumber,

Re: how to display with pre-fix and with pre-fix values

swuehl — Fri, 29 May 2015 10:07:47 GMT

Or using subfield():

LOAD

FieldWithPrefix,

subfield(FieldWithPrefix,'-',1) as Prefix,

subfield(FieldWithPrefix,'-',2) as FieldWithoutPrefix,

Re: how to display with pre-fix and with pre-fix values

ankitaag — Fri, 29 May 2015 10:27:13 GMT

Hi Munna,

So you want 1 qvd with prefix values and 1 with non prefix values?

Re: how to display with pre-fix and with pre-fix values

Fri, 29 May 2015 10:28:59 GMT

If my guess is right for having two tables with and without prefix product number, create a flag and use it as below.

Product:

LOAD CHAN_ID,

PROD_CD,

[Product Number],

Wildmatch([Product Number],'*-*') as Prefixflag,

LOCATOR,

LOCAL_CD,

LOCAL_DESCRP

FROM

MLE.xls

(biff, embedded labels, table is Sheet1$);

NoConcatenate

ProductsWithPrefix:

LOAD *

Resident Product

Where Prefixflag=1;

QUALIFY *;

NoConcatenate

//ProductsWithOutPrefix:

LOAD *

Resident Product

Where Prefixflag=0;

DROP Table Product;

Re: how to display with pre-fix and with pre-fix values

antoniotiman — Fri, 29 May 2015 10:49:45 GMT

Hi,

try in script

Temp:

Load

....

from ....;

Table:

Concatenate

Load

Replace([Product Number],'20-','') as [Product Number]

Resident Temp;

Regards,

Antonio

Re: how to display with pre-fix and with pre-fix values

Srinivas — Fri, 29 May 2015 10:56:59 GMT

No i want to see combination of both values in one qvd.

Regards

munna

Re: how to display with pre-fix and with pre-fix values

daniellial — Fri, 29 May 2015 11:37:36 GMT

I think the solution is to do as Antonio said.

Re: how to display with pre-fix and with pre-fix values

Peter_Cammaert — Fri, 29 May 2015 11:41:13 GMT

It took me a while to discover your exact requirement, but I think I may have a solution for you.

See the attached document. Please correct me if I'm wrong.

Best,

Peter

Re: how to display with pre-fix and with pre-fix values

help4qv123 — Fri, 29 May 2015 12:59:57 GMT

MLE:

LOAD CHAN_ID,

PROD_CD,

[Product Number],

LOCATOR,

LOCAL_CD,

LOCAL_DESCRP

FROM

C:\Users\Downloads\MLE.xls

(biff, embedded labels, table is [Sheet1$]);

NEW:

load

CHAN_ID,

PROD_CD,

[Product Number],

LOCATOR,

if (count(DISTINCT [Product Number]) > 1, concat(DISTINCT [Product Number], ', ')) as test

Resident MLE group by CHAN_ID,

PROD_CD, LOCATOR,

[Product Number];

drop Table MLE;

STORE NEW into ' path ';

Re: how to display with pre-fix and with pre-fix values

help4qv123 — Fri, 29 May 2015 13:00:25 GMT

Gud 1

Re: how to display with pre-fix and with pre-fix values

Srinivas — Fri, 29 May 2015 14:08:35 GMT

Thanks Peter,

I hope its correct in frontend but i need in backend as a qvd like below screenshot way,

Advance Thanks

munna

Re: how to display with pre-fix and with pre-fix values

Srinivas — Mon, 01 Jun 2015 05:05:21 GMT

Yes Ankita,

Could you please suggest to me how to do.and hoe make this.

Re: how to display with pre-fix and with pre-fix values

Mon, 01 Jun 2015 05:24:29 GMT

First of all you have to identify , in which condition you want to display with pre-fix and without pre-fix.

Based on some column you should write IF ELSE (Case Statement in you script) or in application and will work perfectly.

From your Screenshot I can see If LOCAL_DESCRP is not NULL then you want to display with pre-fix else without pre-fix.

So you should write case statement based on column LOCAL_DESCRP or else identify the right column from you data and write case statement.

Re: how to display with pre-fix and with pre-fix values

Srinivas — Mon, 01 Jun 2015 05:50:35 GMT

Hi Shivendoo,

Can you please give me example application.

Re: how to display with pre-fix and with pre-fix values

Peter_Cammaert — Mon, 01 Jun 2015 07:11:28 GMT

How about this?

RawData:

LOAD CHAN_ID,

PROD_CD,

[Product Number],

LOCATOR,

LOCAL_CD,

LOCAL_DESCRP

FROM [.\MLE.xls](biff, embedded labels, table is Sheet1$);

LEFT JOIN (RawData)

LOAD PROD_CD, COUNT(DISTINCT [Product Number]) AS Occurrences

RESIDENT RawData

GROUP BY PROD_CD;

FinalTable:

NOCONCATENATE LOAD * RESIDENT RawData WHERE Occurrences > 1;

DROP Table RawData;

STORE FinalTable INTO [.\MultipleProductNumbers.qvd] (qvd);

See attached document for an example. Modify to suit your needs.

Peter

Re: how to display with pre-fix and with pre-fix values

Srinivas — Mon, 01 Jun 2015 09:18:58 GMT

Thanks Peter,

It's almost correct but I need one more modification , In our scenario displayed when CHAN_ID is different, Here I need wherever CHAN_ID should be same and then product number have pre-fix and without pre-fix value i need to display.see below screenshot ,this is my requirement.

Regards,

Munna

Re: how to display with pre-fix and with pre-fix values

Srinivas — Mon, 01 Jun 2015 10:25:11 GMT

Hello Peter can you please help me on this scenario.

Best,

Munna

Re: how to display with pre-fix and with pre-fix values

Peter_Cammaert — Mon, 01 Jun 2015 11:49:25 GMT

You should have a field that ties rows together (e.g. in your example the two rows have no relation except an identical CHAN_ID). If it isn't present, create one. For example, when loading the data from excel, create a new field without optional prefix, like:

...IF (mid([Product Number], 3, 1) = '-',

mid([Product Number], 4), [Product Number]) AS [Core Product Number], ...

Change the GROUP BY clause of the next LOAD statement into:

GROUP BY CHAN_ID, [Core Product Number];

That's about it.

Peter

Re: how to display with pre-fix and with pre-fix values

Srinivas — Mon, 01 Jun 2015 12:01:01 GMT

Thanks for your support Peter,

Could you please send me sample application.That would be more helpful.

Regards,

Munna