count as vlookup function

[#1]

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 

[#2]

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

[#3]

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

[IAMDV]

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,

DV

www.QlikShare.com

[#5]

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).

[vincent_ardiet · Accepted solution]

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

[#7]

thanks Giampiero

thanks Deepak

thanks Vincent

[vincent_ardiet]

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