topic Re: Data model in QlikView

Data model

jumiprado — Fri, 22 Jul 2016 19:05:36 GMT

Hey Guys, i need some help with this model:

I Have four tables with different information, I attached an excel file with an example.

I need to build a model that will allow me to get a

Make in the same line graph which can analyze the billing and collection for different MonthYear -> If i have more than one Collection date take the last date and SUM the Collection Money for that COLLECTION_ID

For example:

ID	BILL_ID	ID_BILL_ID	BILL_ISSUE_DATE	BILL_ISSUE_DATE_ID	BILLED_MONEY
10054-2	26	10054-2-26	01/12/2014	24168	5100

ID	ID_BILL_ID	BILL_ID	COLLECTION_DATE	COLLECTION_DATE_ID	COLLECTED MONEY
10054-2	10054-2-26	26	01/12/2014	24168	2500
10054-2	10054-2-26	26	01/01/2015	24169	2600

And take something like this:

ID	BILL_ID	ID_BILL_ID	BILL_ISSUE_DATE	BILL_ISSUE_DATE_ID	BILLED_MONEY	COLLECTION_DATE	COLLECTED MONEY
10054-2	26	10054-2-26	01/12/2014	24168	5100	01/01/2015	24169	5100

Get For each ID_BILL_ID the collected money. Check that in cases i have more than one collection date for the same ID.
I think previously i need to group the information in some tables.
Make in the same Bar Chart for each MonthYear where i can count the Suscribes, the Unsuscribes for this MonthYear and the Suspension

-> I tried to make a MasterCalendar For each table but i think thats not the best way.

-> How can i make a good data model for take different indicators, besides those I mention.

Thanks you in advance for your help!

Re: Data model

Sat, 23 Jul 2016 15:55:19 GMT

I realy like concatenate tables, this way i can share dimensions.

FOLOW THE EXAMPLE OF THE MODEL I THINK IT'S REALY GOOD

USE SET ANALISYS FOR YOUR EXPRESSIONS. EXAMPLE : SUM({<FLAG_FATO={"POL"}>} FIELD)

Re: Data model

jumiprado — Mon, 25 Jul 2016 13:26:07 GMT

Thanks you for your help!

I think this is good, but i need to take something more. How can i create this table from the 2 of the top.

D	BILL_ISSUE_DATE	BILL_ISSUE_DATE_ID	BILLED_MONEY
10054-2	26	10054-2-26	01/12/2014	24168	5100

ID	ID_BILL_ID	BILL_ID	COLLECTION_DATE	COLLECTION_DATE_ID	COLLECTED MONEY
10054-2	10054-2-26	26	01/12/2014	24168	2500
10054-2	10054-2-26	26	01/01/2015	24169	2600

And take something like this:

ID	BILL_ID	ID_BILL_ID	BILL_ISSUE_DATE	BILL_ISSUE_DATE_ID	BILLED_MONEY	COLLECTION_DATE	COLLECTED MONEY
10054-2	26	10054-2-26	01/12/2014	24168	5100	01/01/2015	5100

Re: Data model

Mon, 25 Jul 2016 13:34:03 GMT

you can use a dinamic table

Re: Data model

MindaugasBacius — Mon, 25 Jul 2016 13:45:32 GMT

One solution might be:

map_collected_money:
mapping LOAD ID, 
     sum([COLLECTED MONEY])
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @5)
group by ID;


LOAD D, 
  ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],
     BILL_ISSUE_DATE, 
     BILL_ISSUE_DATE_ID, 
     BILLED_MONEY, 
     F5, 
     F6
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @4);

The result is:

Find the attachment.

Re: Data model

jumiprado — Mon, 25 Jul 2016 14:15:32 GMT

Its a good way, if i need the last date collected i have to make a MAX(COLLECTION_DATE) in another mapping load?

Thanks!

Re: Data model

MindaugasBacius — Tue, 26 Jul 2016 05:05:14 GMT

Well than instead of ApplyMap use Left join:

LOAD D as ID, 
// ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],
     BILL_ISSUE_DATE, 
     BILL_ISSUE_DATE_ID, 
     BILLED_MONEY
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @4);


left join
LOAD ID, 
     sum([COLLECTED MONEY]) as [COLLECTED MONEY],
     max(date(date#(COLLECTION_DATE, 'DD/MM/YYYY'), 'YYYY-MM-DD')) as COLLECTION_DATE
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @5)
group by ID;

Results:

The attachment also included.

Re: Data model

jumiprado — Tue, 26 Jul 2016 14:43:34 GMT

mindaugasbacius‌ thanks for your help, i have a question about this. When i am going to make a Line Chart with MonthYear with the unique dimension...am i going to have the Collection Money at the MonthYear that the client paid? I need to make this line chart because I have to view the evolution of Billing and Collections!

Thanks again!

Re: Data model

cheburashka — Thu, 19 Jan 2017 12:14:19 GMT

Hi Juan,

I've created some QvM - Qlikview Modules that I use to help me validate my datamodel.

Maybe you'll find them useful.

,KR Koen

PS: Don't forget to mark answers as helpful and correct.