script editor

[TheresaB_4]

Hi Everyone, 

I am new to Qlik Sense but I am not sure if you can do this in the editor. In sql it takes several joins and temp tables to get the new groupnames. There must be a way to do this in Qliksense.

I have one Fact table with 3 Dimensions

How do I add the 3 different groupnames in the Editor?
This is just a small sample but the real data is very large.

In SQL you will have to do 3 queries for the different groups:
This is the example for group name: InvoiceBilling

select dit.InvoiceTypeName
, ist.InvoiceStatusName
, dmp.MeteringPointReconMonth
, count(fi.InvoiceKey) as NrOfInvoices
,'DirectBilling' As InvoiceName
from [dw].[FactInvoice] fi
join [dw].[DimInvoiceType] dit on fi.InvoiceTypeKey = dit.InvoiceTypeKey
join [dw].[DimInvoiceStatus] ist on fi.InvoiceStatusKey = ist.InvoiceStatusKey
join dw.FactInvoiceContract fic on fic.InvoiceKey = fi.InvoiceKey
join dw.FactContractAssignment fca on fca.ContractKey=fic.ContractKey
join dw.DimContractLine cl on cl.ContractKey=fca.ContractKey
join dw.FactMeteringPointContractLine fmpcl on fmpcl.ContractLineKey=cl.ContractLineKey
join dw.DimMeteringPoint dmp on dmp.MeteringPointKey=fmpcl.MeteringPointKey
Where dmp.MeteringPointReconMonth in ('45','57','59','99') and fi.DataSourceKey = 2
group by dit.InvoiceTypeName, ist.InvoiceStatusName,dmp.meteringPointReconMonth
order by dit.InvoiceTypeName, ist.InvoiceStatusName,dmp.meteringPointReconMonth

example1

[Or]

I wasn't able to follow along with what you're trying to do - where are these new group names supposed to be coming from? What are they based on? In your file it looks like just another column, in which case it should be easy to read, but I'm assuming that's not the case or you wouldn't have asked.

[TheresaB_4]

It is hard to explain.

I want to create 3 different types of invoices based on a criteria. So the new type of invoice will be column.
1. The criteria for DirectBilling:
MeteringPointReconMonth = 45,57,59,99
2. Criteria for AdvancedPayment

InvoiceTypwName = 'AdvancePayment' and InvoiceStatusName='Approved' 

3. The criteria for YearlyInvoices

MeteringPointReconMonth = 51

I hope I explained a bit better.

[Or]

You would typically just write those into if() statements.

If(Match(MeteringPointReconMonth,45,57,59,99),'DirectBilling',

if(InvoiceTypwName = 'AdvancePayment' and InvoiceStatusName='Approved' , 'AdvancedPayment',

if(MeteringPointReconMonth = 51, 'YearlyInvoices','Other'))) as GroupName

[TheresaB_4]

So I can use if statements even if the InvoiceType, InvoiceStatus and ReconMonth are in different tables?

[Or]

Nope. You would want to join them into a single table if that wasn't already the case.

[TheresaB_4]

We have too much data so it is not possible to merge or join it all in one table.

Any other suggestions?

[Or]

Create a table with just the fields you need, use that to decide which rows to keep, and then drop that table.

Regarding "Too much data", the difference between joining tables and keeping them separate is not usually that significant. I'd suggest testing the app size before deciding against joining (your SQL seemed to be joining the tables, so what's the difference?)

[robert99]

• Yes. As long as you have a field by the same name in the different tables. Qlik will automatically do an outer join keep. I rarely join tables . Either use mapping load or join keep

[robert99]

Too much data. The options are merge (concatenate), join keep, join or mapping load. I don't advice joining. But the other 3 should be fine in qlik