topic Re: Problem with the data in a section access qvw in QlikView

Problem with the data in a section access qvw

geogou1973 — Mon, 09 May 2016 13:38:19 GMT

Hello i have the following script in the Sec Tab

Section Access;

LOAD * INLINE [

ACCESS, NTNAME

ADMIN, DOMAINNAME\USERNAME1

USER, DOMAINNAME\USERNAME2

];

Section Application;

STAR IS *;

LOAD * INLINE [

NTNAME, CSECAR

DOMAINNAME\USERNAME1, *

DOMANINAME\USERNAME2, 10

];

I have checked also in the Document Properties and the tab Opening the following

In some cases i am losing data. Did i do anything wrong with the script or the options that i have checked in the opening tab ?

Thank you in advance.

Re: Problem with the data in a section access qvw

swuehl — Mon, 09 May 2016 13:51:56 GMT

Could you detail a bit more the scenarios where you are losing data?

I've had some issues with the STAR IS ...; statement in the past, so I generally try to avoid it.

Instead, I create explicite associations in the table.

Re: Problem with the data in a section access qvw

marcus_sommer — Mon, 09 May 2016 13:54:37 GMT

I think there is nothing wrong with your settings but your used wildcard of "*" in CSECAR won't mandatory cover all records. Are there for example NULL's within this fields they will be excluded by the opening reduction of the application. An indicator for this is if you opened the application as admin (who should see all data) and try to save the application without a reload - if you get a SaveAs dialog for this any data-reduction has happens.

- Marcus

Re: Problem with the data in a section access qvw

krishnacbe — Mon, 09 May 2016 14:02:29 GMT

* will include all the data in the inline statement and not the full list from DB or anyother source.

you need to join the distinct values from table to the section access table so that all the values will be included for Admin.

explain in detail about what data you lose.

- Krishnapriya

Re: Problem with the data in a section access qvw

geogou1973 — Mon, 09 May 2016 14:08:14 GMT

Hello Marcus.

I discover that some data does not much with the CSECAR because is null. When i open the QVW from the server and before save it the data are correct. When i save and i open the qvw from the explorer i have the problem with the data that have no CSECAR. How can i avoid this problem ?

Re: Problem with the data in a section access qvw

marcus_sommer — Mon, 09 May 2016 14:15:03 GMT

For this you need to replace the NULL's with a real value like 'NULL' or '#NV' and it will be depend on how these NULL's are created which way will be suitable to catch them. Maybe a simple if-loop like:

if(len(trim(CSECAR))=0'#NV', CSECAR) as CSECAR

will be enough. If not have a look here: NULL handling in QlikView.

- Marcus

Re: Problem with the data in a section access qvw

geogou1973 — Mon, 09 May 2016 14:16:30 GMT

Hello swuehl.

I discovered that some data does not much with the CSECAR because is null. When i open the QVW from the server and before save it the data are correct. When i save and i open the qvw from the explorer i have the problem with the data that have no CSECAR. How can i avoid this problem ?

Problem with the data in a section access qvw

Re: Problem with the data in a section access qvw

Mon, 09 May 2016 14:27:59 GMT

Hello there,Could this be a problem with the values and the comparison with Uppercase against the Lowercase?

Re: Problem with the data in a section access qvw

swuehl — Mon, 09 May 2016 20:39:33 GMT

QlikView can only link values, not NULL.

And section access' reduction mechanism is based on linking fields in section access and section application.

If you want more help, you would need to describe a bit more detailed how your values in the reduction fields look like.

Re: Problem with the data in a section access qvw

geogou1973 — Tue, 10 May 2016 09:43:20 GMT

Hello.

I have two tables. The one the table for the items and the other is the table for the sales.

The table for the items has the item number and the barcode and the CSECAR (the first level of the category of the item). The table for the sales has the barcode and sales qty and sales amount. The data for the sales are uploading for the stores. In this table some times there are barcodes that are not included in the table of the items because are from the collection of franchise stores. For this reason the data are not matching and do not have CSECAR the field is null and with section access in the script we are losing them.

With which way can i avoid this problem ?

Thank you in advance

Re: Problem with the data in a section access qvw

geogou1973 — Tue, 10 May 2016 09:43:31 GMT

Hello.

I have two tables. The one the table for the items and the other is the table for the sales.

With which way can i avoid this problem ?

Thank you in advance

Re: Problem with the data in a section access qvw

marcus_sommer — Tue, 10 May 2016 09:50:05 GMT

And the approach to check this field for NULL like above mentioned didn't work?

if(len(trim(CSECAR))=0, '#NV', CSECAR) as CSECAR

- Marcus

Re: Problem with the data in a section access qvw

geogou1973 — Tue, 10 May 2016 09:59:58 GMT

Where can i put this command ?

When i load the table for the items ? If i put there it will not find anything because all the items have CSECAR.

Or in the section access and how ?

Re: Problem with the data in a section access qvw

marcus_sommer — Tue, 10 May 2016 10:13:08 GMT

I think this need to come into your Sales table - there where you experience the NULL.

- Marcus

Re: Problem with the data in a section access qvw

geogou1973 — Tue, 10 May 2016 10:17:58 GMT

There is not the field CSECAR in the table for sales. The matching between the two tables becomes with the field barcode.

Re: Problem with the data in a section access qvw

marcus_sommer — Tue, 10 May 2016 10:22:16 GMT

Then you need to check the matching-result of the barcodes which led to the field CSECAR. Maybe you could post this part of the script.

- Marcus

Re: Problem with the data in a section access qvw

geogou1973 — Tue, 10 May 2016 10:31:04 GMT

The data for the sales are uploading from the POS of the stores. The barcode is not null , but some barcodes are not in the collection of the database of the table of items because these barcodes are uploading from franchise stores.

Re: Problem with the data in a section access qvw

marcus_sommer — Tue, 10 May 2016 10:37:20 GMT

Yes, but in some way must these field CSECAR be created or filled within your datamodel which you have in the link-table between your section access and the other data in your datamodel.

- Marcus

Re: Problem with the data in a section access qvw

geogou1973 — Tue, 10 May 2016 11:59:55 GMT

This is the script :

Tab Sec

Section Access;

LOAD * INLINE [

ACCESS, NTNAME

ADMIN, DOMAINNAME\USERNAME1

USER, DOMAINNAME\USERNAME2

];

Section Application;

STAR IS *;

LOAD * INLINE [

NTNAME, CSECAR

DOMAINNAME\USERNAME1, *

DOMANINAME\USERNAME2, 10

];

Tab Data

ITEMS:

LOAD

CEANAR,

BARCODE_ID,

CFAMAR,

CLPRAR,

CAPRAR,

BRAND,

CMETAR,

CRAYAR,

CSECAR,

CSFAAR,

CSLPAR,

CSTAAR,

DEANAR,

LARTAR,

ITEM_ID,

SUPPLIER_ID,

TREFAR,

CREATION_DATE

FROM \\qvdstorage\ITEMS.QVD (qvd);

SALES:

LOAD ITEM_ID,

BARCODE_ID,

STORE_ID,

SALES_DATE,

SALES_WEEK,

SALES_WEEKDAY,

SALES_YEAR,

SALES_MONTH,

SALES_QTY,

SALES_AMT,

CURRENT_SELPRICE,

SALES_PERC_FPA,

SALES_AMT_FPA,

PRICE_LIST,

PRICE_NET_LIST,

PRICE_HALF_NET_LIST,

PRICE_NET_NET_LIST,

COST_SALES

FROM \\qvdstorage\SALES.QVD (qvd);

Re: Problem with the data in a section access qvw

marcus_sommer — Tue, 10 May 2016 12:08:33 GMT

Your field is within the item-table:

....

ITEMS:

LOAD

CEANAR,

BARCODE_ID,

CFAMAR,

CLPRAR,

CAPRAR,

BRAND,

CMETAR,

CRAYAR,

if(len(trim(CSECAR))=0, '#NV', CSECAR) as CSECAR,

//CSECAR,

CSFAAR,

CSLPAR,

CSTAAR,

DEANAR,

LARTAR,

ITEM_ID,

SUPPLIER_ID,

TREFAR,

CREATION_DATE

FROM \\qvdstorage\ITEMS.QVD (qvd);

.....

- Marcus