topic Re: getting all the codes in selection hierarchy? in QlikView

getting all the codes in selection hierarchy?

Fri, 23 Sep 2016 13:02:31 GMT

HI All,

Here I am having all the codes hierarchical order code,

Under N_Code,Having some B_Codes

Under B_Code,Having some C_Codes

Under C_Code,Having some D_Codes

Following order is correct, but here my problem is in HIR_CD....

When i am Selecting N_Code, All the codes in N_Code,B_Codes,C_Codes,D_Codes is showing in HIR_CD as white color, but i need to see only the Specific selected N_Code White color in HIR_CD.

In the similar way,

When i am Selecting B_Code, All the codes in B_Code,C_Code,D_Code is showing in HIR_CD as white color, but i need to see only the Specific selected B_Code as White color in HIR_CD...

Kindly help me on this..

Regards,

Helen

Re: getting all the codes in selection hierarchy?

Fri, 23 Sep 2016 20:47:47 GMT

Under the Field box don't select HIR_CD instead select <Expression>. Then in the Edit Expression box use this code.

=IF(GetSelectedCount(D_CODE)>0,IF(D_CODE=HIR_CD,HIR_CD),IF(GetSelectedCount(C_CODE)>0,IF(C_CODE=HIR_CD,HIR_CD),IF(GetSelectedCount(B_CODE)>0,IF(B_CODE=HIR_CD,HIR_CD),IF(GetSelectedCount(N_CODE)>0,IF(N_CODE=HIR_CD,HIR_CD),HIR_CD))))

This is only allowing the box to show values at the appropriate level. I also set it up to return all values if nothing is selected.

Re: getting all the codes in selection hierarchy?

Sat, 24 Sep 2016 06:00:31 GMT

Thanks Matthew, I need to do this in the Back end part..

Also can you share the app , have u implemented in my app..

Regards,

Helen

Re: getting all the codes in selection hierarchy?

Sat, 24 Sep 2016 06:15:20 GMT

HI Matthew,

you have done in vice versa in the Exp,

Here, If i am selecting anything in the N_Code,B_Codes,C_Codes,D_Codes then the same code should reflect in the HIR_CD..

Kindly check once please..

Regards,

Helen

Re: getting all the codes in selection hierarchy?

Sat, 24 Sep 2016 07:46:36 GMT

I've attached my sample. It is working the way you described or I'm not understanding your scenario. Try it out...

That is how it's working. This doesn't work like a traditional program where I teed to tell HIR_CD to filter when an item gets selected in another box. Because of the hierarchy you set up in the data model that part happens automatically. We just need to help it out to get rid of the extra options. As you can see in your original example D_CODE already worked. That's because there is only 1 valid option for D_CODE.

If IB003411 is selected in C_CODE HIR_CD is limited to the C_CODE and D_CODE options in IB003411 leaving 6 available selections. What I've done in my special expression is removed all of the N_CODE,B_CODE and D_CODE items from the list. With a C_CODE selected and only C_CODE items in the list only one value is level in the list. Check it out and let me know.

Re: getting all the codes in selection hierarchy?

Sun, 25 Sep 2016 15:45:06 GMT

HI Mathew,

Thank u , but i need this ln backend because in my application i have used HIR_CD as composite key to the facts table.. Kindly request u to do in the backend.

regards,

helen

Re: getting all the codes in selection hierarchy?

stabben23 — Mon, 26 Sep 2016 09:23:05 GMT

Hi,

add this in sheet2 in Excel:

NodeID	ParentID	Name
N_CODE		N_CODE
B_CODE	N_CODE	B_CODE
C_CODE	B_CODE	C_CODE
D_CODE	C_CODE	D_CODE

Add this code in script:

HierarchyBelongsTo (NodeID, ParentID, Name, ParentID, AncestorName, DepthDiff)
LOAD NodeID,
ParentID,
Name
FROM

(ooxml, embedded labels, table is Sheet2);

CrossTable(NodeID, HIR_CD, 1)
LOAD '1' as Dummy,
N_CODE,
B_CODE,
C_CODE,
D_CODE
FROM

(ooxml, embedded labels, table is Sheet1);

Re: getting all the codes in selection hierarchy?

stabben23 — Mon, 26 Sep 2016 09:32:29 GMT

If you then need the HIR_CD as a Key do a resident LOAD on the crosstable like this:

temp:
CrossTable(NodeID, HIR_CD, 1)
LOAD '1' as Dummy,
N_CODE,
B_CODE,
C_CODE,
D_CODE

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate
New:
LOAD
NodeID,
HIR_CD
Resident temp;

Drop table temp;