Qualifier Statement

[#1]

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

[perumal_41]

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

[#3]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[perumal_41]

Hi,

Please Upload ur Sample Qlikview File and Xls Sheet.

Regards,

Perumal A

[#6]

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

[#7]

Hi Perumal,

The information is sensitive so I cant upload the xls.

thanks,

Gregg

[perumal_41]

Hi,

i am  Ask   Sample Xls Data only.

[#9]

Hi Both,

I have it working using concatenation.

Thanks,

Gregg