Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I'm in dire need of help here. So, I am building an app. I have a base data sheet (Let's say OrderSheet)which has Order numbers(unique) among other dozens of fields and I have another file(Let's say MappingSheet)which is required to get the Field1 and Field2 info for the order numbers that are available in the base file. The MappingSheet has Order numbers but each Order number is available multiple times depending on how many time Field1 and Field2 values have been updated. So for ex : Order number : 8876324823 has a single record in OrderSheet but multiple records in MappingSheet with different values in Field1 and Field2 logged at different time.
The initial requirement was to map the Order number from OrderSheet in the MappingSheet and simply get the latest record which was easy using ApplyMap().But now client is asking that they want the whole info. so for ex : If Order number 8876324823 has 10 records in MappingSheet then I have to take all the 10 ones and show it in the dashboard not just the latest record.
I thought maybe I'll just load the MappingSheet as a new table and associate it with the OrderSheet table using the Order number field but I am not able to get the desired result in the dashboard. I am very new to QV so maybe i'm missing something very basic here.
I really need to come up with a solution and quickly so any help will be appreciated.
Thanks,
Pranav
You could load with a LEFT KEEP.
LEFT KEEP (OrderSheet) LOAD Fields FROM MappingSheet;
In that case only the order numbers existing in the table: OrderSheet are loaded from the MappingSheet.
HI @pranaview,
@pranaview wrote:
I thought maybe I'll just load the MappingSheet as a new table and associate it with the OrderSheet table using the Order number field but I am not able to get the desired result in the dashboard. I am very new to QV so maybe i'm missing something very basic here.
That is the right way to do it. Are you getting syntetic keys ,or loop error messages? The tables in the datamodel should be linked with one field. For example use; OrderNumber AS %OrderNumber_key in both tables. Then Qlik automatically associates the data.
Hi @pranaview,
You could try; Count.(Order_Number), because Count.(DISTINCT Order_Number) counts only the unique numbers.
Besides that counting on key fields (you can see them in datamodel viewer) can result in wrong quantities .Rename the fields that are linked for example as I mentioned in the previous post. Then only use Order_Number in the order header table and count the order lines from another field.
You could load with a LEFT KEEP.
LEFT KEEP (OrderSheet) LOAD Fields FROM MappingSheet;
In that case only the order numbers existing in the table: OrderSheet are loaded from the MappingSheet.