topic count as vlookup function in QlikView

count as vlookup function

Fri, 01 Jun 2012 07:13:43 GMT

i have 4 columns (sub_code1, sub_code2, sub_code3, sub code4 : you can see in 1st table at attachement)

i want to count the codes in codes.xlsx in all of the four columns

count as vlookup function

Fri, 01 Jun 2012 08:48:36 GMT

Hi,

could you please let us know how you built your data model.

In this way we can understand if is easier to operate in your script or by set analysis.

thanks

Regards

Giampiero

count as vlookup function

Fri, 01 Jun 2012 09:03:33 GMT

first table is my excel (database )

and i want to count codes in codes codes.xlsx

what did you mean with data model. i am sorry i am new to qlikview

Re: count as vlookup function

IAMDV — Fri, 01 Jun 2012 09:09:02 GMT

Hi,

No problem. Don't have to be sorry, we all have gone through same process.

I'm not clear with the question. Do you want to Count them in Excel file? or do you want to count them in QlikView? If it's QlikView then please can you post the sample QVW file?

Thanks,

www.QlikShare.com

count as vlookup function

Fri, 01 Jun 2012 09:11:17 GMT

Thats ok,

it's a pleasure for us to help each other.

For Data model I mean the database that Qlik creates when you import your data by the script (in this case from your xls).

Re: count as vlookup function

vincent_ardiet — Fri, 01 Jun 2012 09:11:42 GMT

Hi,

If your codes in codes.xlsx are loaded in QV (for example in the field SUB_CODE) in an isolated table you can create a chart like this :

3 Dimensions :

- EKIP

- Ay

- SUB_CODE

1 expression :

=Sum(

SubStringCount( DS_DELAY_SUB_CODE_1&'|'&DS_DELAY_SUB_CODE_2&'|'&DS_DELAY_SUB_CODE_3&'|'&DS_DELAY_SUB_CODE_4,

SUB_CODE ) )

Regards,

Vincent

count as vlookup function

Fri, 01 Jun 2012 09:48:43 GMT

thanks Giampiero

thanks Deepak

thanks Vincent

Re: count as vlookup function

vincent_ardiet — Fri, 01 Jun 2012 10:17:02 GMT

I have tryied my solution on your application but it seems that performances are poor.

I your data are going to grow, it will be more efficient to work on the script first, for example creating this table :

ds_delay:

load

ID_Tarih_Ucus,

DS_DELAY_SUB_CODE_1 as Kod,

1 as Level,

1 as Quantity

Resident Parametrik_Ucus

Where DS_DELAY_SUB_CODE_1 <> '?' ;

load

ID_Tarih_Ucus,

DS_DELAY_SUB_CODE_2 as Kod,

2 as Level,

1 as Quantity

Resident Parametrik_Ucus

Where DS_DELAY_SUB_CODE_2 <> '?' ;

load

ID_Tarih_Ucus,

DS_DELAY_SUB_CODE_3 as Kod,

3 as Level,

1 as Quantity

Resident Parametrik_Ucus

Where DS_DELAY_SUB_CODE_3 <> '?' ;

load

ID_Tarih_Ucus,

DS_DELAY_SUB_CODE_4 as Kod,

4 as Level,

1 as Quantity

Resident Parametrik_Ucus

Where DS_DELAY_SUB_CODE_4 <> '?' ;

Then, instead of the substringcount in the expression, just use sum(Quantity).

Regards,

Vincent