How to use Count and Distinct function together in Qlik Sense load script?

[baarathi]

Initially I was fetching data from database So I could follow the below method to fetch data.

Load BillId, MinFill, MaxFill;

SQL SELECT
BillId
, Min(BillNumber) as MinFill
, Max(BillNumber) as MaxFill
FROM dbo.SampleBillData
Where (BillNumber = 1 or BillNumber >= 2)
Group By BillId having Count(DISTINCT TransId) > 1;

Now I am trying to bring similar data from xlsx, By using below method

TempSampleBill:
Load BillId
, Min(BillNumber) as MinFill
, Max(BillNumber) as MaxFill
, TransId
From SampleBillData.xlsx
Where (BillNumber = 1 or BillNumber >= 2);

SampleBill:
Load BillId
, Min(BillNumber) as MinFill
, Max(BillNumber) as MaxFill
Resident TempSampleBill
Where Count(Distinct TransId) >1
Group By BillId;

Drop Table TempSampleBill;

I am getting error that i cannot use count(distinct TransId). How to perform the above operation to get the desired result as I was able to get in SQL Select

[sunny_talwar]

Try this

SampleBill:
Load BillId,
     Min(BillNumber) as MinFill,
     Max(BillNumber) as MaxFill,
     TransId
From SampleBillData.xlsx
Where (BillNumber = 1 or BillNumber >= 2);

Right Join (SampleBill)
LOAD BillId
Where Count > 1;
LOAD BillId,
     Count(Distinct TransId) as Count
Resident SampleBill
Group By BillId;

[baarathi]

@sunny_talwar Count (distinct TransId) as Count is not valid.

[sunny_talwar]

Why not? Did you use the Group by as suggested above?

[baarathi]

Yes i have used exactly the same script.