topic Qualifier Statement in QlikView

Qualifier Statement

Wed, 15 Feb 2012 10:35:23 GMT

Hi,

I am using a Qualifier statment below to prevent synthetic key in my simple script.

However, when I use it, it seems to load the invoices table in for each file.

I have tried to change its precendence but no joy.

If I only bring in one file for each table it works but when I bring in historical data it loads them individually.

Anyone know what I am doing wrong?

Credits:

LOAD [Credit Note Number],

[Acc No] as AccNo,

[Cust Name],

Rep,

[Reason Code],

[Credit Value],

[Profit/Loss],

[New % Margin/Loss],

Date(Date) as Date

FROM

Credits\DSC*.csv

Customer:

LOAD [Acc Number] as AccNo,

Store,

[Pick Group],

Contract,

[Sub Contract]

FROM DSC*.csv

QUALIFY *;

UNQUALIFY AccNo;

Invoices:

LOAD [Invoice No],

[Acc No] as AccNo,

[Cust Name],

Rep,

[Profit/Loss],

[New % Margin/Loss],

[Order Value]

//Date(Date) as date commented out to stop the link creating a synthetic key

FROM DSI*.csv

Qualifier Statement

perumal_41 — Wed, 15 Feb 2012 10:47:14 GMT

Credits:

LOAD [Credit Note Number],

[Acc No] as AccNo,

[Cust Name],

Rep,

[Reason Code],

[Credit Value],

[Profit/Loss],

[New % Margin/Loss],

Date(Date) as Credit_Date

FROM

Credits\DSC*.csv

Customer:

LOAD [Acc Number] as AccNo,

Store,

[Pick Group],

Contract,

[Sub Contract]

FROM DSC*.csv

QUALIFY *;

UNQUALIFY AccNo;

Invoices:

LOAD [Invoice No],

[Acc No] as AccNo,

[Cust Name],

Rep,

[Profit/Loss],

[New % Margin/Loss],

[Order Value] ,

Date(Date) as inv_date commented out to stop the link creating a synthetic key

FROM DSI*.csv

Try this Script

Regards ,

perumal A

Qualifier Statement

Wed, 15 Feb 2012 10:53:22 GMT

Hi perumal A

I included the two new names in my script and they are created but I still have the same issue.

regards,

Gregg

Re: Qualifier Statement

Miguel_Angel_Baeyens — Wed, 15 Feb 2012 10:55:36 GMT

Hi Gregg,

Qualify does not work that way when you concatenate files. Although you are not using the CONCATENATE keyword, it is happening because you are loading multiple files (DSI*.csv). QUALIFY, as you already know, prefixes each field with the name of the table, and each iteration of a concatenation with QUALIFY will end the table label with "-1", "-2" and so on.

So you will have to do two steps. First a noncatenated load of all fields from the DSI*.csv files, store them into QVD files, then using QUALIFY load all fields from this other load. However, in your case, I'd concatenate the Invoices and Credits table as I suggested in a previous post.

Invoices:
LOAD [Invoice No],
     [Acc No] as AccNo,
     [Cust Name],
     Rep,
     [Profit/Loss],
     [New % Margin/Loss],
     [Order Value]
     //Date(Date) as date commented out to stop the link creating a synthetic key
FROM DSI*.csv
STORE Invoices INTO Invoices.qvd (qvd);
DROP TABLE Invoices;
QUALIFY *;
UNQUALIFY AccNo;
Invoices:
LOAD *
FROM Invoices.qvd (qvd);

Hope that helps.

Miguel

Qualifier Statement

perumal_41 — Wed, 15 Feb 2012 10:58:44 GMT

Hi,

Please Upload ur Sample Qlikview File and Xls Sheet.

Regards,

Perumal A

Qualifier Statement

Wed, 15 Feb 2012 11:01:18 GMT

Hi Miguel,

I am going to look into concatenating both tables now.

I had thought of this but not too familiar on how to do it.

Thanks,

Gregg

Re: Qualifier Statement

Wed, 15 Feb 2012 11:08:05 GMT

Hi Perumal,

The information is sensitive so I cant upload the xls.

thanks,

Gregg

Qualifier Statement

perumal_41 — Wed, 15 Feb 2012 11:17:48 GMT

Hi,

i am Ask Sample Xls Data only.

Qualifier Statement

Wed, 15 Feb 2012 12:51:51 GMT

Hi Both,

I have it working using concatenation.

Thanks,

Gregg