topic Re: simple and smart ? in QlikView

simple and smart ?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Folks,

I have data like this

load * inline [

prodcuts,sales,cardgroup

producta,100,cardA

productb,200,cardB

];

here the point I will create a chart with

products as dim

sum(sales) as expression

here I am expecting the thing is

I should have new field called Group

where Group should have data

Group

All

cardA

cardB

if user select on All from Group columns it should show me the data for both Producta and Productb

if he select on cardA then only product ... like wise other one

how to achieve this expect below way

t1:

load

products,

sales,

cardgroup,

cardgroup as Group

...... from qvd1

concatenate

t1:

load

products,

sales,

cardgroup,

'All' as Group

...... from qvd1

the above solution is working ,but I don't want the same table again for getting the all filter

Re: simple and smart ?

Anil_Babu_Samineni — Thu, 10 Aug 2017 02:59:25 GMT

One front end solution, May be this?

Re: simple and smart ?

Anonymous — Thu, 10 Aug 2017 03:03:57 GMT

Required only in Backend

Re: simple and smart ?

Anil_Babu_Samineni — Thu, 10 Aug 2017 03:06:37 GMT

Then your way is correct only

Re: simple and smart ?

Anonymous — Thu, 10 Aug 2017 03:56:49 GMT

Any other way ,

reason why asking is

if my qvd has 10 million records , for "All" flag again am loading 10 million records which I dislike

Re: simple and smart ?

Anil_Babu_Samineni — Thu, 10 Aug 2017 04:19:53 GMT

One possible solution may be this? Because, until unless you need only 3 static names. If need more that 20 then we can do same with For...Each..Loop

Sample:

Mapping Load * inline [

prodcuts,sales,cardgroup

producta,100,cardA

productb,200,cardB

];

Final:

// Auto Join / Auto Concatenate. Don't require to declare any Keyword between directories.

Load products, sales, 'All' as Group, Applymap('Sample', cardgroup, 'NA') as cardgroup Resident Sample;

Load products, sales, 'cardA' as Group, Applymap('Sample', cardgroup, 'NA') as cardgroup Resident Sample;

Load products, sales, 'cardB' as Group, Applymap('Sample', cardgroup, 'NA') as cardgroup Resident Sample;

Drop Table Sample;

Re: simple and smart ?

Anonymous — Thu, 10 Aug 2017 04:22:32 GMT

Hi Anil,

I created a inline table like below

load * inline [

cardgroup,Group

cardA,All

cardB,All

cardA,cardA

cardB,cardB

];

it looks working , let me if I am wrong

Re: simple and smart ?

Anil_Babu_Samineni — Thu, 10 Aug 2017 04:30:00 GMT

You said, you have thousand of hundred records, That is the case you want to do manually for whole records? If so, Not good idea. And although here you have to do some similar cases where Mapping table to each record using ApplyMap(). That may makes lot informative and structured format.

Can i know the reason you want to do this in Script only. Because, My first reply gives the good result until unless brain to storm. Here, I've done one island table to map each together

Re: simple and smart ?

Anonymous — Thu, 10 Aug 2017 04:36:01 GMT

We have couple of filter conditions in ui expression already ,

that too expression are bit lengthy , are we prefer here to do in script rather than ui

Regarding 10 milimoon records is

cardGroup will be always have only cardA or cardB only

but to map 'All' flag am loading full qvd again right which I don't want to do that

let me know if u r not clear on my explination

Re: simple and smart ?

Anil_Babu_Samineni — Thu, 10 Aug 2017 04:46:39 GMT

This is the case, You can map island table to the Fact table where 10M records pulling. Will you?

Re: simple and smart ?

jonathandienst — Thu, 10 Aug 2017 04:55:41 GMT

It is not necessary to load the table data twice - that is total overkill and complicates the front end expressions. Rather do this after the main table load:

t2:

LOAD FieldValue('cardgroup', RowNo()) as cardgroup,

'All' as Group

Autogenerate FieldValueCount('cardgroup')

Concatenate(t2)

LOAD FieldValue('cardgroup', RowNo()) as cardgroup,

cardgroup as Group

Autogenerate FieldValueCount('cardgroup')

Group will now link cardA too itself and to 'All'.

But a better way is not do that at all. Just train your users that no selection is the same as 'All'.

Re: simple and smart ?

Anonymous — Thu, 10 Aug 2017 06:24:18 GMT

am not sure,

is there any effect on performance ?

Re: simple and smart ?

nagarjuna_kotha — Thu, 10 Aug 2017 06:51:59 GMT

Hi,

Its is not required to load twice the same table as per script,

Instead of taking two time same table,

Step 1 : Load the QVD .

Step 2: Take resident of above table (It will take less time as compared to actual load).

//Ur Script:

t1:

load

products,

sales,

cardgroup,

cardgroup as Group

...... from qvd1

concatenate

t1:

load

products,

sales,

cardgroup,

'All' as Group

...... from qvd1

My script:

Data:

load

products,

sales,

cardgroup,

cardgroup as Group

...... from qvd1

concatenate

t1:

load

products,

sales,

cardgroup,

'All' as Group

Resident Data ;

I hope this may helps you.

Regards,

Nagarjun

Re: simple and smart ?

MayilVahanan — Thu, 10 Aug 2017 12:20:25 GMT

Try like this

Prod:

load * inline [

prodcuts,sales,cardgroup

producta,100,cardA

productb,200,cardB

];

load Distinct cardgroup, cardgroup as Group Resident Prod;

Concatenate

LOAD 'All' As Group, cardgroup Resident T;

It does not load all the fields again and also load only distinct cardGroup from ur table. so that, its doesn't occupy memory that much.

Hope it helps

Re: simple and smart ?

Colin-Albert — Thu, 10 Aug 2017 12:25:36 GMT

There is no need for the All group.

In QlikView if you select nothing in a listbox, that is effectively the same as selecting all values.

Just have a list box with CardA and CardB.

Selecting one of the cards will filter on that data, selecting neither will show all data.

Green, White & Grey the fundamentals of selection in QlikView.

Qlik Tips: Raise a flag to the Green, White and Gray